Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Grant Execute Permissions For All Stored Procedures 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 stored procedures but you don't want to make the user a db_owner. The code below will do a GRANT EXECUTE for all the procedures in the DB This line will skip those dt_ procedures that are in every database WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0

Right now this code prints the GRANT EXECUTE statements, change the PRINT to EXEC if you want it to be done automatically

  1. --Grab all the procedures for the current DB
  2. SELECT IDENTITY(INT,1,1) AS ID,
  3. SPECIFIC_NAME
  4. INTO #Procedurelist
  5. FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
  6. WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
  7. AND ROUTINE_TYPE='PROCEDURE'
  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 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 #Procedurelist
  24.  
  25. DECLARE
  26. @SQL VARCHAR(500),
  27. @ProcName VARCHAR(400)
  28.  
  29.  
  30. --This is where the loop starts
  31. WHILE @Loopid <= @MaxId BEGIN
  32.  
  33. --grab the procedure name
  34. SELECT @ProcName = SPECIFIC_NAME
  35. FROM #Procedurelist
  36. WHERE ID = @Loopid
  37.  
  38. --construct the statement
  39. SELECT @SQL = 'GRANT EXECUTE ON ' + @ProcName + ' TO ' + @UserName
  40. PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically
  41.  
  42. --increment counter
  43. SET @Loopid = @Loopid + 1
  44. END
  45.  
  46. --clean up
  47. DROP TABLE #Procedurelist



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

Part of SQL Server Admin Hacks

330 Rating: 3.5/5 (2 votes cast)