Grant EXECUTE Permissions on all Stored Procedures to a Single User
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;
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.