Tuesday, October 7, 2014

Microsoft SQL Useful Database Role to Execute Stored Procedures: db_executor

I find it convenient to create a db_executor database role to give users the ability to execute stored procedures. This way you can look at the user's roles and just know they have the ability to execute stored procedures. Otherwise you have to go look at the permissions at the database level and most people forget or don't know to look there. You create the role as follows:

-- Create a db_executor role
CREATE ROLE db_executor 

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor 

Then add the user to the role as follows, replacing <UserAccount> with the name of the user:

EXEC sp_addrolemember N'db_executor', N'<UserAccount>'

or if you have a newer version of SQL server:

ALTER ROLE [db_executor] ADD MEMBER [<UserAccount>]

No comments:

Post a Comment