Technical Article

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