SQL grant在多个对象上执行 [英] SQL grant execute on multiple objects

问题描述

您好，我想为多个对象的用户添加执行权限。

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

这有效，但我有很多存储过程，以XU_开始，现在我想授予执行所有以XU开头的存储过程_

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.

你可能做的是这样的 - SQL Server生成这些语句为您：

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

此查询将列出以 XU 开头的所有过程，并创建一个包含 GRANT EXECUTE ON ....

This query will list all procedures that start with XU and create a column that contains the GRANT EXECUTE ON .... statement for that procedure.

在SQL Server Management Studio中运行此脚本，然后将所生成的 GrantCmd 列，将其粘贴到新窗口，然后在那里执行。

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.

/ strong>要自动化这个，你也可以把这个查询转换成一个游标，然后使用动态SQL来自动执行 GrantCmd 语句....

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

这篇关于SQL grant在多个对象上执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！