Granting execute permissions can be stored procedures can be quite a pain, especially when you have dropped and recreated your stored procedures when moving between environments.
You can go to each stored procedure and right click each one and change the permissions. That takes forever.
A quick way to resolve the issue (in SQL 2005 and SQL 2008) is to create a role and give it execute permissions at that database level.
Here is the SQL to run:
/* CREATE A NEW ROLE */ CREATE ROLE db_executor /* GRANT EXECUTE TO THE ROLE */ GRANT EXECUTE TO db_executor
This will create a SQL role and anyone you put in it will will have execute permissions to all stored procedures in the database.
This doesn’t work if you only want to grant execute to a subset of stored procedures.