Script to Grant execute permissions on SP and UDFs

CREATE   PROCEDURE dbo.sp_GrantPermissions (@strUserName VARCHAR(400))
-- By:  MAB  
-- Date:  04 Nov 2003
-- Purpose: To grant execute permissions on sps and UDFs.

--Parameters
--@strUserName: user name to 

AS
DECLARE @strObjName  VARCHAR(1000)
DECLARE @strCmd  VARCHAR(1000)
 
DECLARE selobj CURSOR LOCAL FOR
SELECT     name
FROM         sysobjects
WHERE     (type = 'P'  OR type = 'FN'  ) AND (category = 0)
 
OPEN selObj
FETCH FROM selObj INTO @strObjName 
-- PRINT 'Object Name = ' +  @strObjName
 
WHILE @@FETCH_STATUS = 0
BEGIN 
 SET @strCmd = 'GRANT  EXECUTE  ON ' + @strObjName + ' TO ' + @strUserName
 PRINT @strCmd
 PRINT ''
 EXEC(@strCmd) 
 FETCH NEXT FROM selObj INTO @strObjName  
END
CLOSE selObj
DEALLOCATE selObj



DECLARE selobj CURSOR LOCAL FOR
SELECT     name
FROM         sysobjects
WHERE     (type = 'IF' OR type='V') AND (category = 0)
 
OPEN selObj
FETCH FROM selObj INTO @strObjName 
-- PRINT 'Object Name = ' +  @strObjName
 
WHILE @@FETCH_STATUS = 0
BEGIN 
 SET @strCmd = 'GRANT  SELECT  ON ' + @strObjName + ' TO ' + @strUserName
 PRINT @strCmd
 PRINT ''
 EXEC(@strCmd) 
 FETCH NEXT FROM selObj INTO @strObjName  
END
CLOSE selObj
DEALLOCATE selObj
GO