Hi,

One of my store procedure is doing a select in 2 different databases. I'm getting a select permission error. This store procedure is in production and i don't want to give select permission on any table to the users. The users have exec rights on the store procedure and they have public rights to both databases. The exec SP is find within it's own database, but when a store procedure trys to query another database i get that permission error. Any suggestion?

Thanks

-- Anonymous, March 29, 2001

Answers

Eric,

Read the article in SQL Server Books Online titled, "Ownership Chains". This explains that you can avoid select permission errors when the stored procedure owner also owns the underlying tables. The best way to assure this is to have the dbo create the stored procedure. Mind you, not someone merely aliased as dbo!

In practice, only sa is the true dbo. Thus, you will most likely eliminate the problems by going in as sa and recreating the stored procedures in question.

Good Luck,

Eric

-- Anonymous, March 30, 2001