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 myDomaindbUsers, but get a syntax error when the script tries to execute this statement:


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

+ '].[' + @StoredProcedure

+ '] TO myDomaindbUsers'


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 domaingroup.
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 myDomaindbUsers'

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