Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.