Grant Execute Permissions For All Stored Procedures To A User
From Wiki
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
- --Grab all the procedures for the current DB
- SELECT IDENTITY(INT,1,1) AS ID,
- SPECIFIC_NAME
- INTO #Procedurelist
- FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
- WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
- AND ROUTINE_TYPE='PROCEDURE'
- ORDER BY SPECIFIC_NAME
- DECLARE
- @Loopid INT,
- @MaxId INT,
- @UserName VARCHAR(50)
- --This is the user that will get the execute permissions
- SELECT @UserName = 'SomeUser'
- --Grab start and end values for the loop
- SELECT @Loopid = 1,
- @MaxId = MAX(ID)
- FROM #Procedurelist
- DECLARE
- @SQL VARCHAR(500),
- @ProcName VARCHAR(400)
- --This is where the loop starts
- WHILE @Loopid <= @MaxId BEGIN
- --grab the procedure name
- SELECT @ProcName = SPECIFIC_NAME
- FROM #Procedurelist
- WHERE ID = @Loopid
- --construct the statement
- SELECT @SQL = 'GRANT EXECUTE 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 #Procedurelist
Contributed by: --SQLDenis 14:35, 3 June 2008 (GMT)
Part of SQL Server Admin Hacks
