SQL Server has several fixed database roles
such as db_datareader
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
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 :
Alternatively the user can be added to the role in code using the following SQL :
-- to allocate a user to the new role :
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.