Granting Execute Permissions to a user in SQL2005

I found it a chore to create execute permissions on all stored procedures in a database.

This query will do the job for you:

CREATE A ROLE AS FOLLOWS:

1.
/* CREATE A NEW ROLE */
CREATE ROLE db_executor

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

2.
AND THEN when creating user, assign db_executor as role rather than dbo.


3.To assign user to all stored procedures in database run this.

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0