Bulk Granting EXECTUTE Permission on SQL Server

Here's some code I regularly find useful when going from a dev server to a production server (or changing the username), I'm sure there's a more automated way but for a quick fix...

DECLARE @username varchar(100)
SET @username = 'xyz'
SELECT
    'GRANT EXECUTE ON ' + name + ' TO ' + @username
FROM
    sysobjects
WHERE
    xtype = 'p'
AND
     LEFT(name, 4) = 'PRE_'

The code is simple, it lists all the stored procedures with a set prefix and generates the T-SQL to grant EXECUTE permissions to a given user.

To use it, set the username, update the last line if you would like to limit it's bounds (we prefix ours for clarity i.e. SProcs with a prefix of CMS_ are used in the Content Management System) and run it, then copy the results and run them in a Query Analyser.