Pakistan Web Development Consultant Adeel Sarfraz is a consultant based in Pakistan giving out tips, tricks and code samples

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