SQL 2008 R2 - How to grant stored procedure access read access to a table

I wish to grant a stored procedure read access to a table in SQL Server 2008 R2

I will then grant a user access only to the stored procedure, not to the tabl开发者_Python百科e itself.

How do I do this?

Grant EXEC access to the single stored procedure and no permissions to the table.

Grant EXECUTE On [SpName] To [Principal]

There is no such concept as a read permissions to a table for a stored procedure. Stored procedure permissions are separate from other objects. If a user has permission to run a stored procedure, they can run it no matter what it does, even if they don't have permission to the underlying objects.

The following article is a very detailed look at several different methods for granting permissions using stored procedures. It's definitely worth a read.

http://www.sommarskog.se/grantperm.html

Most of the time, ownership chaining will take care of permissions problems. As long as the stored procedure and the underlying table have the same owner, the user only needs permission to EXECUTE the stored procedure.

In a comment to another answer, you mention that the stored procedure is in a different database from the table. You might look at module signing as a method for allowing access only through the stored procedure. Another alternative, if possible, is to create the stored procedure in the same database as the table, then grant access to users in the other database. That way, ownership chaining will take effect.