GRANT EXEC or SELECT to sprocs, UDFs and views

-- TSQL Script to GRANT EXEC to all excutable objects and 
-- GRANT SELECT to selectable non-table objects (ie views and UDFs)
-- By Peter Daniels
-- 12/30/2003

DECLARE @i int
DECLARE @n int
DECLARE @strAccount sysname
DECLARE @strSQL varchar(128)

SET @strAccount = 'RoleExecAndView'

SELECT
IDENTITY(int) AS PKID,
CASE
WHEN type IN ('P', 'FN') THEN 'EXECUTE'
WHEN type IN ('IF', 'TF', 'V') THEN 'SELECT'
END AS Permission,
[name] AS ObjectName
INTO
#tmpObject
FROM 
dbo.sysobjects
WHERE
type IN('P', 'FN', 'IF', 'TF', 'V') AND 
(category = 0)

SET @n = @@ROWCOUNT
SET @i = 1

WHILE (@i <= @n) BEGIN
SELECT
@strSQL = 'GRANT ' + Permission + ' ON dbo.[' + ObjectName + '] TO [' + @strAccount + ']'
FROM
#tmpObject 
WHERE
PKID = @i

PRINT @strSQL
EXEC(@strSQL)

SET @i = @i + 1
END

DROP TABLE #tmpObject