授予对存储过程的权限Grant Permissions on a Stored Procedure

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题说明如何在 SQL Server 2017SQL Server 2017 中使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL授予对存储过程的权限。This topic describes how to grant permissions on a stored procedure in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 可以为数据库中的现有用户、数据库角色或应用程序角色授予权限。Permissions can be granted to an existing user, database role, or application role in the database.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 不能使用 SQL Server Management StudioSQL Server Management Studio 授予对系统过程或系统函数的权限。You cannot use SQL Server Management StudioSQL Server Management Studio to grant permissions on system procedures or system functions. 改为使用 GRANT 对象权限Use GRANT Object Permissions instead.

SecuritySecurity

权限Permissions

授权者(或用 AS 选项指定的主体)必须具有带 GRANT OPTION 的相同权限,或具有隐含所授予权限的更高权限。The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted. 需要拥有对该过程所属架构的 ALTER 权限,或对该过程的 CONTROL 权限。Requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure. 有关详细信息,请参阅 GRANT 对象权限 (Transact-SQL)授予对存储过程的权限。For more information, see GRANT Object Permissions (Transact-SQL).

使用 SQL Server Management StudioUsing SQL Server Management Studio

授予对存储过程的权限To grant permissions on a stored procedure

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” 、过程所属的数据库以及 “可编程性”Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. 展开“存储过程” ,右键单击要针对其授予权限的过程,再单击“属性” 。Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.

  4. “存储过程属性” 中,选择 “权限” 页。From Stored Procedure Properties, select the Permissions page.

  5. 若要为用户、数据库角色或应用程序角色授予权限,请单击 “搜索”To grant permissions to a user, database role, or application role, click Search.

  6. “选择用户或角色” 中,单击 “对象类型” 以添加或清除所需的用户和角色。In Select Users or Roles, click Object Types to add or clear the users and roles you want.

  7. 单击 ”浏览“ 以显示用户或角色列表。Click Browse to display the list of users or roles. 选择应对其授予权限的用户或角色。Select the users or roles to whom permissions should be granted.

  8. “显式权限” 网格中,选择要为指定的用户或角色授予的权限。In the Explicit Permissions grid, select the permissions to grant to the specified user or role. 有关权限的说明,请参阅权限(数据库引擎)For a description of the permissions, see Permissions (Database Engine).

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

选择 “授予” 指示要为被授权者授予指定的权限。Selecting Grant indicates the grantee will be given the specified permission. 选择 “具有授予权限” 指示被授权者还可以将指定权限授予其他主体。Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.

使用 Transact-SQLUsing Transact-SQL

授予对存储过程的权限To grant permissions on a stored procedure

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击 “执行”Copy and paste the following example into the query window and click Execute. 该示例授予名为 EXECUTE 的应用程序角色对存储过程 HumanResources.uspUpdateEmployeeHireInfoRecruiting11权限。This example grants EXECUTE permission on the stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role named Recruiting11.

USE AdventureWorks2012;   
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo  
    TO Recruiting11;  
GO  

另请参阅See Also

sys.fn_builtin_permissions (Transact-SQL) sys.fn_builtin_permissions (Transact-SQL)
GRANT 对象权限 (Transact-SQL) GRANT Object Permissions (Transact-SQL)
创建存储过程 Create a Stored Procedure
修改存储过程 Modify a Stored Procedure
删除存储过程 Delete a Stored Procedure
重命名存储过程Rename a Stored Procedure