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
JWardRogers wrote: