Developing Matt

My Technical Journal

Grant EXECUTE Permissions on all Stored Procedures to a Single User by Jeremiah Peschka and Aaron Bertrand

leave a comment »

I’ve stolen this from Jeremiah Pescha who stole it from Aaron Bertrand who either wrote it himself or stole it from you. 

If ‘stole’ is too harsh replace ‘stole’ with ‘modified.’  Regardless, this is a handy little script that I didn’t want to lose in my ever growing ‘my documents’ folders.  Thank you Mr.Pescha and Mr. Bertrand for making my life easier.

— 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(30);

SET @sql = N”
SET @newline = NCHAR(13) + NCHAR(10);
SET @user_name = N’DOMAIN\USERGROUP’;
— escaping _ prevents it from matching any single character
— including the wildcard makes this much more portable between DBs
SET @sproc_name_pattern = N’%’;

— 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;
print @sql
EXEC sp_executesql @sql;

Advertisements

Written by matt

August 11, 2009 at 3:31 pm

Posted in T-Sql

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: