1

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.

theog
  • 111
  • 3

1 Answers1

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68