How to grant execute permission on all sprocs?

Execute the following script generator Microsoft SQL Server T-SQL query in SSMS Query Editor after setting the results to TEXT mode. Paste the resulting script back to the query window for deployment execution:

USE pubs;

DECLARE @DBrole sysname

SET @DBrole = 'RoleDELTA'

-- SQL execute permission check

PRINT 'Not permissioned'

SELECT name FROM sysobjects

WHERE xtype='P' AND base_schema_ver < 16

AND id NOT IN

            ( SELECT o.id FROM syspermissions p

                  LEFT JOIN sysusers u ON p.grantee = u.uid

                  LEFT JOIN sysobjects o ON p.id = o.id

                  WHERE lower(u.name) = lower(@DBrole)

            )

-- SQL grant execute - generate statements

PRINT 'GRANT EXECUTE generation '

SELECT 'GRANT EXECUTE ON [' + name + '] TO [' + @DBrole + ']'

FROM sysobjects

WHERE xtype='P' AND base_schema_ver <> 16

      AND id NOT IN

            ( SELECT o.id FROM syspermissions p

                  LEFT JOIN sysusers u ON p.grantee = u.uid

                  LEFT JOIN sysobjects o ON p.id = o.id

                  WHERE lower(u.name) = lower(@DBrole)

            )

GO

/* Partial results

GRANT EXECUTE ON [byroyalty] TO [RoleDELTA]

GRANT EXECUTE ON [CustomerListByState] TO [RoleDELTA]

*/

Related article:

http://www.sqlusa.com/bestpractices/grantexectorole/