SQL grant execute on multiple objects

Question

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.

Answer 1

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