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.