Showing posts with label db_excutor. Show all posts
Showing posts with label db_excutor. Show all posts

Thursday, July 14, 2016

db_executor role for user permission to execute all procedures

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
 
--then add a member to this role