Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We've got about 55 vendor databases running in SQL Server 2000 compability mode on a SQL Server 2008 server. Cross database ownership chaining is enabled.

We also have our own in-house reporting database on this server with nothing but stored procedures in it. These stored procs query the vendor database tables for our reporting purposes.

All vendor objects and reporting stored procedures are owned by dbo.

(I know this is not an ideal setup. Putting that aside for now...)

Can someone tell me, when I grant execute on the reporting stored procs to user X, why do I also have to give user X explicit select permissions on the affected vendor database tables (the ones that the stored procs query)?

I thought that, as long as ownership chaining was enabled and all objects had the same ownership, that execute rights on the stored proc would be sufficient, but they fail saying that user X does not have select rights on the tables.

Thanks in advance for any help.

share|improve this question

1 Answer 1

If you have ownership chaining enabled on both databases, and the user that is running the stored procedure is a member of the vendor's database then they shouldn't need rights to the tables.

The exception is if you are using dynamic SQL, in which case you'll need to grant access to the base tables and security chaining doesn't apply to dynamic SQL as the dynamic SQL is executed out of scope of the original stored procedure execution.

share|improve this answer
    
Thanks for the help Denny. –  theog Aug 17 '10 at 16:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.