Hi all I want to add execute permissions to a user for multiple objects. But I can't seem to add wildcards into my code.
GRANT EXECUTE ON OBJECT::dbo.CREATESERVERSESSIONS TO [domain\user];
this works but I have a lot of stored procedures that start with XU_ now I want grant execute on all stored procedures that start with XU_
GRANT EXECUTE ON OBJECT::dbo.XU_* TO [domain\user];
but that is not working. I hope someone knows a solution to this. Thanks in advance.
You cannot use wildcards - you have to grant either to all objects (or all objects in a schema) - or then you have to list all objects one by one.
What you might do is something like this - have SQL Server generate those statements for you:
SELECT
p.Name,
GrantCmd = 'GRANT EXECUTE ON OBJECT::' + p.name + ' TO [domain\user]'
FROM sys.procedures p
WHERE p.Name LIKE 'XU%'
This query will list all procedures that start with XU and create a column that contains the GRANT EXECUTE ON .... statement for that procedure.
Run this in SQL Server Management Studio, and then just copy the resulting GrantCmd column, paste it to a new window, and execute it there.
And if you really want to automate this, you could also turn this query into a cursor and then use dynamic SQL to automatically execute those resulting GrantCmd statements....
