Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Grant Execute/SELECT Permissions For All User Defined Functions To A User

From Wiki

Jump to: navigation, search

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

  1. --Grab all the functions for the current DB
  2. SELECT IDENTITY(INT,1,1) AS ID,
  3. SPECIFIC_NAME,DATA_TYPE
  4. INTO #FunctionList
  5. FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
  6. WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
  7. AND ROUTINE_TYPE='FUNCTION'
  8. ORDER BY SPECIFIC_NAME
  9.  
  10. DECLARE
  11. @Loopid INT,
  12. @MaxId INT,
  13. @UserName VARCHAR(50)
  14.  
  15.  
  16. --This is the user that will get the execute/select permissions
  17. SELECT @UserName = 'SomeUser'
  18.  
  19.  
  20. --Grab start and end values for the loop
  21. SELECT @Loopid = 1,
  22. @MaxId = MAX(ID)
  23. FROM #FunctionList
  24.  
  25. DECLARE
  26. @SQL VARCHAR(500),
  27. @ProcName VARCHAR(400) ,
  28. @Permission VARCHAR(20),
  29. @DataType VARCHAR(20)
  30.  
  31.  
  32. --This is where the loop starts
  33. WHILE @Loopid <= @MaxId BEGIN
  34.  
  35. --grab the function name and type
  36. SELECT @ProcName = SPECIFIC_NAME, @DataType =DATA_TYPE
  37. FROM #FunctionList
  38. WHERE ID = @Loopid
  39.  
  40. --Find out if it's a table-valued function
  41. IF @DataType ='TABLE'
  42. SELECT @Permission ='SELECT'
  43. ELSE
  44. SELECT @Permission ='EXECUTE'
  45.  
  46.  
  47. --construct the statement
  48. SELECT @SQL = 'GRANT ' + @Permission +' ON [' + @ProcName + '] TO ' + @UserName
  49. PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically
  50.  
  51. --increment counter
  52. SET @Loopid = @Loopid + 1
  53. END
  54.  
  55. --clean up
  56. DROP TABLE #FunctionList



Contributed by:--SQLDenis 14:31, 3 June 2008 (GMT)


Part of SQL Server Admin Hacks

329 Rating: 2.3/5 (4 votes cast)