Add a db_executor role

[Originally posted by]: http://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx

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

A user can then be added to the new role, much like the db_datareader and db_datawriter roles.

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 :

Adding a db_executor role

Alternatively the user can be added to the role in code using the following SQL :

-- to allocate a user to the new role :
EXEC sp_addrolemember 'db_executor','SQLMatters'

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.

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/Adding a db_executor role.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s