[Originally posted by]: http://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx
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 :
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.
Link back to this article : http://www.sqlmatters.com/Articles/Adding a db_executor role.aspx