Grant Execute to all Stored Procedures

Solution 1 : Dynamic SQL -

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO LoginName' -- Replace LoginName with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

Solution 2 : Procedure -

CREATE PROCEDURE USP_gen_CreateGrants AS
DECLARE @ExecSQL varchar(100)
DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

OPEN curGrants
FETCH NEXT FROM curGrants
INTO @ExecSQL

WHILE @@FETCH_STATUS = 0
BEGIN
Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END
Print @ExecSQL
FETCH NEXT FROM curGrants INTO @ExecSQL
END

CLOSE curGrants
DEALLOCATE curGrants