Granting Execute permission on All Stored Procedure

How to grant Execute permission on All Stored Procedure for a new User in SQL Server?


Most of the application have users who are not creator (owner) of the Stored Procedures. Hence the users don't have EXECUTE permission on any of the Stored Procedures by default, considering that they are not part of any existing Role that has execute permission.
 
I have created a small script which returns a string that grants permission to the new User to all the Stored Procedures. Here is the script:
 
SELECT 'GRANT EXECUTE ON ' + name + ' [UserName]'
FROM sysobjects WHERE xtype IN ('P')
 
You need to copy the script generated by above query and execute. Alternatively, you can use below script - where you need not to copy the script generated by above query. Just execute the below script in required database.

DECLARE @UserName [varchar](128), @SQL [varchar](1000)
SET @UserName = '[UserName]'


DECLARE Cur CURSOR FOR
   SELECT 'GRANT EXEC ON ' +
   '[' + u.[Name] + ']' + '.' +
   '[' + o.[Name] + ']' +
   ' TO ' + @UserName  AS SQLCommand
FROM dbo.sysobjects o
JOIN dbo.sysusers u
  ON o.[uid] = u.[uid]
WHERE o.[Type] = 'P'

OPEN Cur
FETCH NEXT FROM Cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SQL
   EXEC (@SQL)
   FETCH NEXT FROM Cur INTO @SQL
END
CLOSE Cur
DEALLOCATE Cur

Cheers !!!