Grant EXEC permission on all stored procedures in SQL Server 2005
If you have a large number of stored procedures in your SQL Server 2005 and you have to give EXEC permission to a database user on all of them then the best way to do it is by running the following SQL
SELECT ' GRANT EXEC ON '+ name +' TO <database_user> ' from sys.procedures
The above statement will generate your GRANT statements and you will just have to copy and run them. That’s it really.
Hope the above helped