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 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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.