asked
Granting permission to stored procedure but not the table
Can I give someone the permission of a stored procedure and not any other database objects? In the stored procedures I am truncating, updating, deleting from tables. Do I need to give them permission of those tables as well? My goal is to deny them doing anything to that table other than what’s in the stored procedure. Also, I want them to just have the access of that one particular stored procedure. Can I grant permission to just one stored procedures or do I need to create it in a separate database?
You can grant execute privileges to just the stored procedure without granting any access to the underlying objects. But, the owner of the stored procedure must have access to those underlying objects in order for this to work. The syntax you want is: GRANT EXECUTE... There is an [example here][1] along with further explanation. [1]:
http://msdn.microsoft.com/en-us/library/ms188371.aspx
14 People are following this question.
