I have to run similar code on a regular basis, so I thought I would share it with everyone.
Basically, it grants access on every stored procedure that meets a certain naming pattern to a single user.
-- I've updated this with Aaron Bertrand's suggestions from the comments.
-- Thanks to Aaron for helping make this better!
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @newline AS NVARCHAR(2);
DECLARE @user_name AS NVARCHAR(100);
DECLARE @sproc_name_pattern AS NVARCHAR(10);
SET @sql = N''
SET @newline = NCHAR(13) + NCHAR(10);
SET @user_name = N'jeremiah';
-- escaping _ prevents it from matching any single character
-- including the wildcard makes this much more portable between DBs
SET @sproc_name_pattern = N'sproc[_]%';
-- using QUOTENAME will properly escape any object names with spaces
-- or other funky characters
SELECT @sql = @sql
+ N'GRANT EXECUTE ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.'
+ QUOTENAME([name])
+ N' TO '
+ QUOTENAME(@user_name)
+ N';'
+ @newline + @newline
FROM sys.procedures
WHERE [name] LIKE @sproc_name_pattern;
-- this is my version of debug code, I usually run it once with the PRINT intact
-- before I actually use sp_executesql
--PRINT @sql;
EXEC sp_executesql @sql;
Comments
9 Comments so far. Leave a comment below.Three suggestions:
(1) escape _ (otherwise it is treated as “any single character”)
(2) store the whole pattern in the string (that way if you move this to a procedure you don’t have to change the procedure to support LIKE ‘%[_]foo’ etc.)
(3) QUOTENAME() object names and include schema prefix in output
SET @sproc_name_pattern = N’sproc[_]%’;
SELECT @sql = @sql N’GRANT EXECUTE ON ‘
+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + ‘.’
+ QUOTENAME([name])
+ ‘ TO ‘
+ QUOTENAME(@user_name)
+ ‘;’
+ @newline + @newline
FROM sys.procedures
WHERE [name] LIKE @sproc_name_pattern;
Note that OBJECT_SCHEMA_NAME() requires 2005 SP2+, IIRC
Very true, OBJECT_SCHEMA_NAME is a newer function. You can also use SCHEMA_NAME([schema_id]) to get the schema name on older releases of SQL Server 2005.
Sorry, as I tweeted, I am always using the newer function because many catalog views and dynamic management views/functions do not include schema_id. And I’d rather use the function than write out the joins by hand. I’m lazy. And thankfully am not managing any pre-SP2 SQL Server 2005 instances.
No worries, I can definitely see why you would prefer OBJECT_SCHEMA_NAME() over SCHEMA_NAME(). Special thanks to both John Keyes (twitter.com/jckeyes) and Brian Davis (twitter.com/brian78) for confirming that SCHEMA_NAME() works on SQL Server 2005 RTM and SP1.
I love this trick! Here’s my version in case you’re interested:
Declare @schema_owner varchar(20);
Set @schema_owner = ‘dbo’;
Select [name] As ’storedProcedure’
, ‘Grant Execute On ‘ + @schema_owner + ‘.’ + [name]
+ ‘ To [insertDatabaseRoleHere];’ As ’sqlCode’
From sys.objects With (NoLock)
Where [name] Not In (
Select o.name
From sys.database_permissions p With (NoLock)
Inner Join sys.objects o With (NoLock)
On p.major_id = o.object_id
Inner Join sys.database_principals u With (NoLock)
On u.principal_id = p.grantee_principal_id
Where u.name = ‘[insertDatabaseRoleHere]‘
)
And [type] = ‘P’;
There’s a couple of differences between our scripts. Your script is used to set up a brand new user, and mine is to check for missing permissions for an existing database role. Also, you automatically execute, and mine you have to copy/paste/execute. But overall, pretty much the same thing, just different ways to get there.
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember ‘db_executor’, ‘<>’
This would work if you’re looking to do it for all stored procedures.
My version is here
http://wiki.lessthandot.com/index.php/Grant_Execute_Permissions_For_All_Stored_Procedures_To_A_User
–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
Thanks Jeremiah. Very simple solution that saves alot of time and works without looping.