Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know that it's possible to set execute permissions for a stored procedure to a database role or user. My question is, how is access to any tables that the SP accesses determined? Does the principal who calls the SP also have to have requisite permissions for the underlying objects that are touched by the SP?

share|improve this question
    
Eric Smith thank you for your clear explanation on this subject. Makes a lot of sense. –  user28726 Sep 26 '13 at 14:28
add comment

1 Answer

up vote 8 down vote accepted

One only needs to ensure that the SP and objects that the SP accesses all have the same owner (ownership chaining rules)--then SQL Server doesn't look at permissions of chained objects. From the online SQL Server documentation:

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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