Monday, May 4, 2015

SQL Server Stored Procedure Alter / View / Execute Permissions

There are different ways to grant user permission to alter, view or execute stored procedures.
The easiest approach I found was to create a "Database Role".
Name anything you like. I liked "db_executor" role.
Now give this role permissions and then add users to this role.

So..

/* Create a new role for stored procedure permissions */
CrEATE ROLE db_executor

/* Grant stored procedure Alter/View/Execute rights to the role */
GRANT ALTER, VIEW DEFINITION, EXECUTE TO [db_executor]

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', [domain\userid]  -- No single quotes around user.

No comments: