Ownership issue problems..... (I think)

[Chris]

I created a database on my laptop, and because I was hoping to keep
the data, I tried detaching it and copying the files to my server and
attaching the database to the server.

I quickly noticed that some of my SPs didn't have rights that I was
expecting them to have. One SP executes a line of dynamic SQL that
updates a record and I get errors saying it doesn't have SELECT
permission or UPDATE permission.

So... I removed the database from the server and scripted the DB.

I logged in to Query Analyzer as 'sa' and ran the create scripts.
The DB lists 'sa' as the owner, and everything in the database lists
'dbo' as the owner, but running those SPs still gives me the same
errors.

Any clues? What can I try to further diagnose this problem?

[Dan Guzman]

Re: Ownership issue problems..... (I think)

Dynamic SQL breaks the ownership chain. Consequently, users need
permissions on the objects directly referenced in the dynamic SQL statement.

You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
SQL Considerations.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Chris" <cmay@walshgrou p.com> wrote in message
news:44f6e099.0 401222324.352e9 d5@posting.goog le.com...[color=blue]
> I created a database on my laptop, and because I was hoping to keep
> the data, I tried detaching it and copying the files to my server and
> attaching the database to the server.
>
> I quickly noticed that some of my SPs didn't have rights that I was
> expecting them to have. One SP executes a line of dynamic SQL that
> updates a record and I get errors saying it doesn't have SELECT
> permission or UPDATE permission.
>
> So... I removed the database from the server and scripted the DB.
>
> I logged in to Query Analyzer as 'sa' and ran the create scripts.
> The DB lists 'sa' as the owner, and everything in the database lists
> 'dbo' as the owner, but running those SPs still gives me the same
> errors.
>
> Any clues? What can I try to further diagnose this problem?[/color]

[Chris]

Re: Ownership issue problems..... (I think)

Thanks Dan!

This really helps.

The article you linked too is really good also, thanks.



"Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message news:<X5aQb.208 84$q4.11340@new sread3.news.atl .earthlink.net> ...[color=blue]
> Dynamic SQL breaks the ownership chain. Consequently, users need
> permissions on the objects directly referenced in the dynamic SQL statement.
>
> You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
> SQL Considerations.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Chris" <cmay@walshgrou p.com> wrote in message
> news:44f6e099.0 401222324.352e9 d5@posting.goog le.com...[color=green]
> > I created a database on my laptop, and because I was hoping to keep
> > the data, I tried detaching it and copying the files to my server and
> > attaching the database to the server.
> >
> > I quickly noticed that some of my SPs didn't have rights that I was
> > expecting them to have. One SP executes a line of dynamic SQL that
> > updates a record and I get errors saying it doesn't have SELECT
> > permission or UPDATE permission.
> >
> > So... I removed the database from the server and scripted the DB.
> >
> > I logged in to Query Analyzer as 'sa' and ran the create scripts.
> > The DB lists 'sa' as the owner, and everything in the database lists
> > 'dbo' as the owner, but running those SPs still gives me the same
> > errors.
> >
> > Any clues? What can I try to further diagnose this problem?[/color][/color]