GRANT EXECUTE 给所有存储过程

发布于 2025-01-07 04:05:28 字数 105 浏览 0 评论 0原文

以下命令是否有效地授予用户“MyUser”执行数据库中所有存储过程的权限？

GRANT EXECUTE TO [MyDomain\MyUser]

原文

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database?

GRANT EXECUTE TO [MyDomain\MyUser]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时常饿 2025-01-14 04:05:28

SQL Server 2008 及更高版本：

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

仅适用于用户（不是角色）：

USE [DBName]
GO
GRANT EXECUTE TO [user]

SQL Server 2008 and Above:

/* 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]

回复收藏 0 原文

无语# 2025-01-14 04:05:28

SQL Server 2005 引入了授予数据库执行权限的功能到数据库原则，正如您所描述的：

GRANT EXECUTE TO [MyDomain\MyUser]

这将在数据库范围内授予权限，其中隐式包括所有模式中的所有存储过程。这意味着您不必为每个存储过程显式授予权限。

您还可以通过授予架构执行权限如果你想更细化：

GRANT EXECUTE ON SCHEMA ::dbo TO [MyDomain\MyUser]

SQL Server 2005 introduced the ability to grant database execute permissions to a database principle, as you've described:

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 permissions if you want to be more granular:

GRANT EXECUTE ON SCHEMA ::dbo TO [MyDomain\MyUser]

回复收藏 0 原文

゛时过境迁 2025-01-14 04:05:28

除了上面的答案之外，我还想补充一点：

您可能希望将其授予角色，然后将该角色分配给用户。
假设您已经创建了一个角色 myAppRights via

CREATE ROLE [myAppRights]

授予执行权限

GRANT EXECUTE TO [myAppRights]

，那么您可以通过 via向该角色

。或者，如果您想在架构级别上执行此操作：

GRANT EXECUTE ON SCHEMA ::dbo TO [myAppRights]

也可以（在此示例中，角色 myAppRights 将拥有架构 dbo< 的所有元素的执行权限） /code> 之后）。

这样，您只需执行一次，并且在以后需要更改时可以轻松地向用户分配/撤销所有相关的应用程序权限 - 如果您想创建更复杂的访问配置文件，则特别有用。

注意：如果您向架构授予角色，这也会影响您稍后创建的元素 - 这可能有益或无益，具体取决于您想要的设计，因此请记住这一点。

In addition to the answers above, I'd like to add:

You might want to grant this to a role instead, and then assign the role to the user(s).
Suppose you have created a role myAppRights via

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 myAppRights will have execute rights on all elements of schema dbo afterwards).

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.

回复收藏 0 原文

~没有更多了~