Tuesday, January 19, 2010

Allow a User to Run All Stored Procedure in a Database

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GRANT EXECUTE TO db_executor

This creates a role called db_executor. Anyone given this role will be able to run all the stored procedures in the database, including new ones added after the user is given the role.


You can either give the user the executor role via SSMS or you can run this sql command against the database you want the user to have access on.

EXEC sp_addrolemember 'db_executor', [LOGIN_NAME]

For Active Directory domain names use '[domain\username]'