Often when a new login has been created and the user mapped to a login the next step is to grant EXECUTE to all Stored Procedures in a database. The following script illustrates how to generate a script to that will grant EXECUTE on all Stored Procedures in a database:
DECLARE @user SYSNAME
SET @user = ‘guest’
SELECT ‘GRANT EXEC ON [‘ + ROUTINE_SCHEMA + ‘].[‘ + ROUTINE_NAME + ‘] TO [‘ + @user + ‘]’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’
