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

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?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@.walshgroup.com> wrote in message
news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> 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?|||Thanks Dan!

This really helps.

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

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<X5aQb.20884$q4.11340@.newsread3.news.atl.earthlink. net>...
> 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@.walshgroup.com> wrote in message
> news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> > 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?