Grant Execute/SELECT Permissions For All User Defined Functions To A User
From Wiki
You want to add a new user with read and write access and also the ability to execute all user defined functions but you don't want to make the user a db_owner. The code below will do a GRANT EXECUTE/SELECT for all the user defined functions in the DB If the user defined function is a table-valued function then you need to grant select permissions otherwise you need to grant execute permissions
Right now this code prints the GRANT EXECUTE/SELECT statements, change the PRINT to EXEC if you want it to be done automatically
- --Grab all the functions for the current DB
- SELECT IDENTITY(INT,1,1) AS ID,
- SPECIFIC_NAME,DATA_TYPE
- INTO #FunctionList
- FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
- WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
- AND ROUTINE_TYPE='FUNCTION'
- ORDER BY SPECIFIC_NAME
- DECLARE
- @Loopid INT,
- @MaxId INT,
- @UserName VARCHAR(50)
- --This is the user that will get the execute/select permissions
- SELECT @UserName = 'SomeUser'
- --Grab start and end values for the loop
- SELECT @Loopid = 1,
- @MaxId = MAX(ID)
- FROM #FunctionList
- DECLARE
- @SQL VARCHAR(500),
- @ProcName VARCHAR(400) ,
- @Permission VARCHAR(20),
- @DataType VARCHAR(20)
- --This is where the loop starts
- WHILE @Loopid <= @MaxId BEGIN
- --grab the function name and type
- SELECT @ProcName = SPECIFIC_NAME, @DataType =DATA_TYPE
- FROM #FunctionList
- WHERE ID = @Loopid
- --Find out if it's a table-valued function
- IF @DataType ='TABLE'
- SELECT @Permission ='SELECT'
- ELSE
- SELECT @Permission ='EXECUTE'
- --construct the statement
- SELECT @SQL = 'GRANT ' + @Permission +' ON [' + @ProcName + '] TO ' + @UserName
- PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically
- --increment counter
- SET @Loopid = @Loopid + 1
- END
- --clean up
- DROP TABLE #FunctionList
Contributed by:--SQLDenis 14:31, 3 June 2008 (GMT)
Part of SQL Server Admin Hacks
