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.