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 : https://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 by Injy93 on Tue 14 Nov 2017 09:33. Report Inappropriate Post

Hi,

How to know if EXECUTE is granted to db_executor ?

Thanks
Post by chandu on Mon 22 Jan 2018 07:46. Report Inappropriate Post

Hi,


Nice Information, very helpful :)
Post by Raj on Wed 23 May 2018 14:39. Report Inappropriate Post

I did this but few Sp's are missing execute permission. Any Idea why?
Post by Raj on Wed 23 May 2018 14:39. Report Inappropriate Post

I did this but few Sp's are missing execute permission. Any Idea why?
Post by EZ on Tue 18 Jun 2019 15:43. Report Inappropriate Post

Excellent!!!

Esto me facilitó el trabajo!!!!!!!

:)
Post by AWS training in hyderabad on Mon 28 Sep 2020 12:36. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
Post by AWS training in hyderabad on Fri 09 Oct 2020 09:04. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
Post by Yasser Ogier on Thu 28 Jan 2021 07:48. Report Inappropriate Post

Excellent Article!

This is by far the most straight forward and straight to the point explanation of the Executor Permissions.
Thanks for this. It helps me a lot.

The article flow with code and screenshots creates a lot of clarity between the 2 setups.

Post by Kusum Bhardwaj on Fri 25 Feb 2022 22:07. Report Inappropriate Post

Great post. thank you!

Post a comment   No login required !

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