Script to grant execute for sprocs

Hello.

I'm using what looks to be a popular script to grant execute privileges to stored procedures, and it works great as long as the user account that you want to grant to is not a domain account.

For example, I need to grant execute to myDomain\dbUsers, but get a syntax error when the script tries to execute this statement:

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

Incorrect syntax near '\'.

The script works fine if a non-concatenated user account is given.

We use Active Directory to manage our access, thus the domain\group.

Has anyone found a way around this?

Thanks in advance.

Tess

Here's the entire script for anyone who's interested:

USE whateverDatabase

GO

DECLARE @.SQL nvarchar(4000),

@.Owner sysname,

@.StoredProcedure sysname,

@.RETURN int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0

AND name LIKE 'p%'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

-- Set the return code to 0

SET @.RETURN = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@.@.FETCH_STATUS = 0) AND (@.RETURN = 0))

BEGIN

-- Create the SQL Statement. Since we’re giving

-- access to all stored procedures, we have to

-- use a two-part naming convention to get the owner.

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

-- Execute the SQL statement

EXEC @.RETURN = sp_executesql @.SQL

-- Get the next row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@.RETURN = 0)

BEGIN

-- Exited fine, commit the permissions

COMMIT TRAN

END

ELSE

BEGIN

-- Exited with an error, rollback any changes

ROLLBACK TRAN

-- Report the error

SET @.SQL = 'Error granting permission to ['

+ @.Owner + '].[' + @.StoredProcedure + ']'

RAISERROR(@.SQL, 16, 1)

END

GO

Just like with table and other object names, if the user contains special characters it must be "quoted" so, add either [] or "" around the user

|||

Thank you very much!

Tess

sql