Adding db_executor role In SQL Server


Recently, One of our colleagues asked me to give permission to execute Stored procedures on the Sales database but he is already having db_datareader, and db_datawriter on the Sales database
but he was unable to execute stored procedures. SQL Server has many fixed database roles including db_datareader, and db_datawriter which allows the user to read and write all the tables in the database but There is no role to allow the user to execute stored procedures in a database but this is very easy to resolve by creating a new role.

 


Below SQL Scripts Creating new role in a database and grants it to execute rights:-


  •  Creating db_executor role
     CREATE ROLE db_executor
  •  Granting execute rights to new role
     GRANT EXECUTE TO db_executor

After that we can add user’s to the new role, like other roles db_datareader and db_datawriter . Now we can check that the role has been created and then we can add user’s to this role like below steps:-


  • Using SQL Server Management Studio

Go to SQL Server Management Studio -> right click on a user in the database -> 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 :


  •  Using T-SQL

 

  •  Assign user to new role :-
EXEC sp_addrolemember 'db_executor','Testlogin'

 


Now a user has been added to this role and will be able to execute all stored procedures in the database.

 

Leave a Reply

Your email address will not be published. Required fields are marked *