1

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

2 Answers 2

Reset to default
2

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
5
  • Forgot about chaining... I never use it. Thanks for picking me up on that one! Commented Dec 3, 2009 at 22:07
  • If someone is the owner of an object I don't see why they would require execute permissions at all? Commented Dec 3, 2009 at 22:11
  • 1
    owners are owners and callers are callers. Nobody said enything about granting EXECUTE to the owner. Commented Dec 3, 2009 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? Commented Dec 7, 2009 at 15:13
  • Read this first msdn.microsoft.com/en-us/library/ms188676.aspx Commented Dec 7, 2009 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
2
  • I assume you mean in a across databases, as this is NOT the case within the same database. Commented Dec 3, 2009 at 22:08
  • Yes, as per your question. Commented Dec 3, 2009 at 23:13

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.