Grant EXEC Rights (Specific)

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'<USER_NAME>’;
— escaping _ prevents it from matching any single character
— including the wildcard makes this much more portable between DBs
SET @sproc_name_pattern = N’spu_%’;

— 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;
–Uncomment Print below to see results
–PRINT @sql;

–Uncomment Exec below to run
–EXEC sp_executesql @sql;

Advertisements

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