0

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.

|improve this question
1

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.

|improve this answer
  • Forgot about chaining... I never use it. Thanks for picking me up on that one! – squillman Dec 3 '09 at 22:07
  • If someone is the owner of an object I don't see why they would require execute permissions at all? – Brettski Dec 3 '09 at 22:11
  • 1
    owners are owners and callers are callers. Nobody said enything about granting EXECUTE to the owner. – Remus Rusanu Dec 3 '09 at 23:09
  • So what your saying is that if the owner of the sproc (in db a) is the same as the owner of the tables (in db b), then me, as non-owner, only needs execute rights to the sproc? – Brettski Dec 7 '09 at 15:13
  • Read this first msdn.microsoft.com/en-us/library/ms188676.aspx – Remus Rusanu Dec 7 '09 at 16:35
0

No, you must also grant SELECT on the schema or the specific objects in question. Granting EXECUTE on the schema just allows the user to do just that. You must also have the permissions to perform the underlying taks within the stored procedure.

|improve this answer
  • I assume you mean in a across databases, as this is NOT the case within the same database. – Brettski Dec 3 '09 at 22:08
  • Yes, as per your question. – squillman Dec 3 '09 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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