Adding a db_executor role

Category : Tips User Rating : 5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :
-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor
A user can then be added to the new role, much like the db_datareader and db_datawriter roles.

If you want to check that the role has been created and then add a user to the role, right click on a user in the database in SQL Server Management Studio and select ‘Properties’. In the ‘Database role membership’ control notice that the new db_executor role now appears, click the checkbox to add the user to the role, as below :

Adding a db_executor role

Alternatively the user can be added to the role in code using the following SQL :
-- to allocate a user to the new role :
EXEC sp_addrolemember 'db_executor','SQLMatters'
A user added to this role will be able to execute all stored procedures in the database, including ones created in the future.

This works for SQL Server 2005 onwards.

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/Adding a db_executor role.aspx

Keywords

SQL,permissions,user,role


Comments
Post by Pradeep on Fri 29 Nov 2013 11:00. Report Inappropriate Post

Thank you very much..
Post by upas on Sat 14 Dec 2013 19:52. Report Inappropriate Post

Good information. Screenshot helped a lot!
Post by Raghav on Thu 20 Feb 2014 22:41. Report Inappropriate Post

Thanks. It worked for me.
Post by vinh on Sun 21 Dec 2014 19:25. Report Inappropriate Post

thank you so much !!!
Post by Larry Smith on Tue 23 Dec 2014 17:33. Report Inappropriate Post

Well done, sir. Thank you for the article.
Post by Ariel on Fri 13 Feb 2015 17:06. Report Inappropriate Post

Excelente, gracias
Post by Reinaldo on Fri 17 Apr 2015 18:36. Report Inappropriate Post

Does it work with types as well?
Post by Sridhar Adi on Fri 05 Jun 2015 12:16. Report Inappropriate Post

Was really helpful..Thank you.
Post by Johnf144 on Fri 23 Oct 2015 23:19. Report Inappropriate Post

I was very pleased to discover this website. I wanted to thank you for your time for this fantastic read!! ebgegkdeeecf
Post by David on Thu 29 Oct 2015 17:20. Report Inappropriate Post

Solved my issue. Great article.
Post by Dave on Mon 09 Nov 2015 04:04. Report Inappropriate Post

I'm able to grant execute through the sp properties and adding the role there in Permissions and checking execute. However, even though I get a 'Command(s) completed successfully on running 'grant execute to db_executor', the role is not being added to the stored procedures. Any ideas?
Post by Andrea on Fri 19 Feb 2016 15:31. Report Inappropriate Post

Hi, Dave. The person who wrote the code did a great work but If i'm not mistaken I suggest that he adds a "USE [MyDB]" in his SQL code otherwise people could try ( like you probably did ) to run that query and would end up creating the db_executor role on the master DB or maybe on any DB they are on in that particular moment!
Post by sam on Wed 06 Jul 2016 20:12. Report Inappropriate Post

Awesome... very helpful
Post by shaik on Wed 31 Aug 2016 06:09. Report Inappropriate Post

Great information, and I agree with Andrea, please make that change....

Keep posting
Post by jim on Thu 08 Sep 2016 23:27. Report Inappropriate Post

Thanks Great
Post by Victor on Wed 18 Jan 2017 19:23. Report Inappropriate Post

Hi.

This should work for functions as well right? Not only stored procs.
Post by subbarao on Wed 05 Apr 2017 07:08. Report Inappropriate Post

HI,

Thank you very much :)

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered