Chris May

Many times you can have a database that has access to it restricted to SPs.  If the database is application specific, then there is a good chance that 1 user account will need to access all these SPs to run the application.

My friend Phil found and updated this script to run in SQL Server 2005 to generate the necessary code to grant execute rights to all SPs in a database for a given user.

SELECT 'GRANT EXECUTE ON ' + sysobjects.name + ' TO AccountName' + 

CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) 

FROM sysobjects 

WHERE type = 'P' and category = 0 AND name not like 'sp_%'