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