In an MS SQL 2005 instance there are two databases.
A specific user has execute rights on the dbo schema for both of these databases.
In database A I have a stored procedure which contains a select statement on a table in database B.
Are the dbo schema execute permissions sufficient for this call, or will the user have to have select access on the table in database B?
We use stored procedures for all database access and would prefer the user only have execute rights.
.gif){kind=link}
Best Answer
If cross db ownership chaining is enabled and the owner of stored procedure in A is also the owner of tables in B (same SID, not just same name) then the EXECUTE permission is sufficient.
If either of these is not true then the current executing login must also have an user in B with SELECT permission on the table. The alternative is to use code signing which can give a more granular control, allowing the user to select from the table only when executing the procedure.