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.