Grant EXECUTE Permissions on all Stored Procedures to a Single User

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”;

SET @sproc_name_pattern = N’test[_]%’; — Pattern name: select stored procedure which starts with test_

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;

EXEC SP_EXECUTESQL @SQL;

If you have an error says “OBJECT_SCHEMA_NAME …”, you will need to upgrade your SQL Server to 2005 SP2 at least.

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