GRANT EXEC or SELECT to sprocs, UDFs and views

,

Based on moffan76's script to GRANT EXEC to all sprocs and UDFs (that EXEC) and GRANT SELECT to views and UDFs (that SELECT).  This script uses that same general technique of selecting the object names from sysobjects, but does it in one step w/o using a cursor.  It is also not wrapped in a sproc like maffan76's.  There is one correction to maffan76's as well - adding the table valued UDFs - type 'TF'.

-- 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

Rate

Share

Share

Rate