MS SQL stored procedure access (inherit)

Question

Hey Guys, 

I wonder is there way to provide execute permission on MS SQL 2005 STORED PROCEDURE for a user, but don't provide the user any permission on TABLE? i.e. I need to restrict user to make any modification on TABLE directly, he should be able do this only through STORED PROCEDURE.

Answer 1

Artak,

This is exactly one of reasons to use stored procedures.  The basic principle is called "ownership chains" and is described here for SQL Server 2005:

http://msdn.microsoft.com/en-us/library/ms188676(SQL.90).aspx

Basically, however, if your stored procedure and the tables it uses are in the same database you can:

GRANT EXECUTE ON TheStoredProc TO Joe
REVOKE SELECT ON SecretTable FROM Joe

In this case, Joe has no rights to select from SecretTable.  However, the owner of TheStoredProc has rights to select from SecretTable.  Therefore, when Joe is granted execute rights on TheStoredProc, the rights of the stored procedure owner are used to access SecretTable.

The key is (1) grant rights to the stored procedures and (2) revoke rights from the tables and/or views. 

RLF