Assign EXECUTE on all Stored Procedures

A little script for assigning EXECUTE (or which ever setting) on all user made stored procedures in a database for SQL Server 2005

DECLARE @USER VARCHAR(255)

SET @USER = 'DATABASE_USER_HERE'

DECLARE sp_CURSOR CURSOR

FOR

SELECT name FROM sys.all_objects

WHERE is_ms_shipped = 0

AND TYPE = ‘P’

DECLARE @Name VARCHAR(255)

OPEN sp_CURSOR

FETCH NEXT FROM sp_CURSOR INTO @Name

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC(‘GRANT EXECUTE ON ’+ @Name +‘ TO ’ + @USER)

FETCH NEXT FROM sp_CURSOR INTO @Name

END

CLOSE sp_CURSOR

DEALLOCATE sp_CURSOR