SQL 对所有存储过程执行 GRANT EXECUTE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9321334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GRANT EXECUTE to all stored procedures
提问by Chad
Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database?
以下命令是否有效地授予用户“MyUser”执行数据库中所有存储过程的权限？
GRANT EXECUTE TO [MyDomain\MyUser]
回答by Antony Scott
SQL Server 2008 and Above:
SQL Server 2008 及更高版本：
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
For just a user (not a role):
对于仅用户（不是角色）：
USE [DBName]
GO
GRANT EXECUTE TO [user]
回答by Robin Minto
SQL Server 2005 introduced the ability to grant database execute permissionsto a database principle, as you've described:
SQL Server 2005 引入了向数据库原则授予数据库执行权限的功能，如您所述：
GRANT EXECUTE TO [MyDomain\MyUser]
That will grant permission at the database scope, which implicitly includes all stored procedures in all schemas. This means that you don't have to explicitly grant permissions per stored procedure.
这将授予在数据库范围内的权限，其中隐式包括所有模式中的所有存储过程。这意味着您不必为每个存储过程显式授予权限。
You can also restrict by granting schema execute permissionsif you want to be more granular:
如果您想更细化，还可以通过授予架构执行权限来进行限制：
GRANT EXECUTE ON SCHEMA ::dbo TO [MyDomain\MyUser]
回答by Matt
In addition to the answers above, I'd like to add:
除了上面的答案，我想补充：
You might want to grant this to a roleinstead, and then assign the role to the user(s).
Suppose you have created a role myAppRightsvia
您可能希望将其授予某个角色，然后将该角色分配给用户。假设您myAppRights通过以下方式创建了一个角色
CREATE ROLE [myAppRights]
then you can give execute rights via
然后你可以通过
GRANT EXECUTE TO [myAppRights]
to that role.
到那个角色。
Or, if you want to do it on schema level:
或者，如果您想在架构级别执行此操作：
GRANT EXECUTE ON SCHEMA ::dbo TO [myAppRights]
also works (in this example, the role myAppRightswill have execute rights on all elements of schema dboafterwards).
也可以工作（在本例中，该角色之后myAppRights将拥有对架构的所有元素的执行权限dbo）。
This way, you only have to do it once and can assign/revoke all related application rights easily to/from a user if you need to change that later on - especially useful if you want to create more complex access profiles.
这样，您只需执行一次，并且可以轻松地向/从用户分配/撤销所有相关的应用程序权限，如果您稍后需要更改该权限 - 如果您想创建更复杂的访问配置文件，则尤其有用。
Note:If you grant a role to a schema, that affects also elements you will have created later - this might be beneficial or not depending on the design you intended, so keep that in mind.
注意：如果您向架构授予角色，这也会影响您稍后创建的元素 - 这可能有益或不利，具体取决于您想要的设计，因此请记住这一点。
回答by Sharon AS
GRANT EXECUTE TO [ROLE]
授予执行 [Role]
This one surely help
这个肯定有帮助
