Granting Execute Permissions to SQL Users

Granting exectution permission to users in SQL used to mean giving them DBO or granting permission to each stored proc. individually.

Last year I found a stored proc that would grant permissions when called but when new stored procs are created, you have to re-run it.

Today I found out that there is a way in SQL 2005 and later to create a new role that has execute permissions to all with the following Code:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

After you have executed that script you can then see the role in the permissions list and assign it there. The user will then have permission to stored procedures added later.