GRANT (Transact-SQL)GRANT (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

为主体授予安全对象的权限。Grants permissions on a securable to a principal. 一般概念是 GRANT <某种权限> ON <某个对象> TO <某个用户、登录名或组>。The general concept is to GRANT <some permission> ON <some object> TO <some user, login, or group>. 有关权限的一般讨论,请参阅权限(数据库引擎)For a general discussion of permissions, see Permissions (Database Engine).

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
-- Simplified syntax for GRANT  
GRANT { ALL [ PRIVILEGES ] }  
      | permission [ ( column [ ,...n ] ) ] [ ,...n ]  
      [ ON [ class :: ] securable ] TO principal [ ,...n ]   
      [ WITH GRANT OPTION ] [ AS principal ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
GRANT   
    <permission> [ ,...n ]  
    [ ON [ <class_type> :: ] securable ]   
    TO principal [ ,...n ]  
    [ WITH GRANT OPTION ]  
[;]  
  
<permission> ::=  
{ see the tables below }  
  
<class_type> ::=  
{  
      LOGIN  
    | DATABASE  
    | OBJECT  
    | ROLE  
    | SCHEMA  
    | USER  
}  

参数Arguments

ALLALL
不推荐使用此选项,保留此选项仅用于向后兼容。This option is deprecated and maintained only for backward compatibility. 它不会授予所有可能的权限。It does not grant all possible permissions. 授予 ALL 等同于授予下列权限:Granting ALL is equivalent to granting the following permissions:

  • 如果安全对象是数据库,则 ALL 对应 BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。If the securable is a database, ALL means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.

  • 如果安全对象是标量函数,则 ALL 对应 EXECUTE 和 REFERENCES。If the securable is a scalar function, ALL means EXECUTE and REFERENCES.

  • 如果安全对象是表值函数,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a table-valued function, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • 如果安全对象是存储过程,则 ALL 表示 EXECUTE。If the securable is a stored procedure, ALL means EXECUTE.

  • 如果安全对象是表,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a table, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • 如果安全对象是视图,则 ALL 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。If the securable is a view, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

PRIVILEGESPRIVILEGES
包含此参数是为了符合 ISO 标准。Included for ISO compliance. 请不要更改 ALL 的行为。Does not change the behavior of ALL.

permission permission
权限的名称。Is the name of a permission. 下面列出的子主题介绍了不同权限与安全对象之间的有效映射。The valid mappings of permissions to securables are described in the subtopics listed below.

columncolumn
指定表中将授予权限的列的名称。Specifies the name of a column in a table on which permissions are being granted. 需要使用圆括号 ()。The parentheses () are required.

class class
指定将授予权限的安全对象的类。Specifies the class of the securable on which the permission is being granted. 需要使用作用域限定符 :: 。The scope qualifier :: is required.

securable securable
指定将授予权限的安全对象。Specifies the securable on which the permission is being granted.

TO principal TO principal
主体的名称。Is the name of a principal. 可为其授予安全对象权限的主体随安全对象而异。The principals to which permissions on a securable can be granted vary, depending on the securable. 有关有效的组合,请参阅下面列出的子主题。See the subtopics listed below for valid combinations.

GRANT OPTIONGRANT OPTION
指示被授权者在获得指定权限的同时还可以将指定权限授予其他主体。Indicates that the grantee will also be given the ability to grant the specified permission to other principals.

AS principal AS principal
使用 AS principal 子句指示:记录为权限授予者的主体应为执行该语句的用户以外的主体。Use the AS principal clause to indicate that the principal recorded as the grantor of the permission should be a principal other than the person executing the statement. 例如,假设用户 Mary 是 principal_id 12,用户 Raul 是主体 15。For example, presume that user Mary is principal_id 12 and user Raul is principal 15. Mary 执行 GRANT SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; 现在,即使语句的实际执行者是用户 13 (Mary),sys.database_permissions 表仍将指示 grantor_prinicpal_id 为 15 (Raul)。Mary executes GRANT SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; Now the sys.database_permissions table will indicate that the grantor_prinicpal_id was 15 (Raul) even though the statement was actually executed by user 13 (Mary).

通常不建议使用 AS 子句,除非需要显式定义权限链。Using the AS clause is typically not recommended unless you need to explicitly define the permission chain. 有关详细信息,请参阅权限(数据库引擎)中的“权限检查算法摘要”部分 。For more information, see the Summary of the Permission Check Algorithm section of Permissions (Database Engine).

在此语句中使用 AS 并不意味着能够模拟其他用户。The use of AS in this statement does not imply the ability to impersonate another user.

RemarksRemarks

GRANT 语句的完整语法非常复杂。The full syntax of the GRANT statement is complex. 上面的语法关系图进行了简化以突出其结构。The syntax diagram above was simplified to draw attention to its structure. 下面列出的文章介绍了在授予特定安全对象权限时使用的完整语法。Complete syntax for granting permissions on specific securables is described in the articles listed below.

REVOKE 语句可用于删除已授予的权限,DENY 语句可用于防止主体通过 GRANT 获得特定权限。The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a principal from gaining a specific permission through a GRANT.

授予权限将删除对所指定安全对象的相应权限的 DENY 或 REVOKE 权限。Granting a permission removes DENY or REVOKE of that permission on the specified securable. 如果在包含该安全对象的更高级别拒绝了相同的权限,则 DENY 优先。If the same permission is denied at a higher scope that contains the securable, the DENY takes precedence. 但是,在更高级别撤消已授予权限的操作并不优先。But revoking the granted permission at a higher scope does not take precedence.

数据库级权限在指定的数据库范围内授予。Database-level permissions are granted within the scope of the specified database. 如果用户需要另一个数据库中的对象的权限,请在该数据库中创建用户帐户,或者授权用户帐户访问该数据库以及当前数据库。If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

注意

表级 DENY 并不优先于列级 GRANT。A table-level DENY does not take precedence over a column-level GRANT. 保留了权限层次结构中这种不一致性以保持向后兼容。This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. 未来的版本会将其删除。It will be removed in a future release.

sp_helprotect 系统存储过程可报告对数据库级安全对象的权限。The sp_helprotect system stored procedure reports permissions on a database-level securable.

WITH GRANT OPTIONWITH GRANT OPTION

GRANT ...WITH GRANT OPTION 指定向接收权限的安全主体提供向其他安全帐户授予指定权限的能力 。The GRANT ... WITH GRANT OPTION specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts. 当接收权限的主体是某一角色或某一 Windows 组时,如果需要进一步将对象权限授予不是该组成员或该角色的用户,则必须使用 AS 子句 。When the principal that receives the permission is a role or a Windows group, the AS clause must be used when the object permission needs to be further granted to users who are not members of the group or role. 因为只有用户(而非某个组或角色)才能执行 GRANT 语句,所以,在授予权限时,该组或角色的特定成员必须使用 AS 子句显式调用该角色或组成员身份 。Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role must use the AS clause to explicitly invoke the role or group membership when granting the permission. 下面的示例说明如何在授予角色或 Windows 组时使用 WITH GRANT OPTION 。The following example shows how the WITH GRANT OPTION is used when granted to a role or Windows group.

-- Execute the following as a database owner  
GRANT EXECUTE ON TestProc TO TesterRole WITH GRANT OPTION;  
EXEC sp_addrolemember TesterRole, User1;  
-- Execute the following as User1  
-- The following fails because User1 does not have the permission as the User1  
GRANT EXECUTE ON TestMe TO User2;  
-- The following succeeds because User1 invokes the TesterRole membership  
GRANT EXECUTE ON TestMe TO User2 AS TesterRole;  

SQL Server 权限图表Chart of SQL Server Permissions

若要获取 pdf 格式的所有 数据库引擎Database Engine 权限的海报大小的图表,请参阅 https://aka.ms/sql-permissions-posterFor a poster sized chart of all 数据库引擎Database Engine permissions in pdf format, see https://aka.ms/sql-permissions-poster.

权限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. 如果使用 AS 选项,则还应满足其他要求。If using the AS option, additional requirements apply. 有关详细信息,请参阅特定于安全对象的文章。See the securable-specific article for details.

对象所有者可以授予对其所拥有的对象的权限。Object owners can grant permissions on the objects they own. 对某安全对象具有 CONTROL 权限的主体可以授予对该安全对象的权限。Principals with CONTROL permission on a securable can grant permission on that securable.

被授予 CONTROL SERVER 权限的用户(例如,sysadmin 固定服务器角色的成员)可以授予对相应服务器中任一个安全对象的任意权限。Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can grant any permission on any securable in the server. 将某一数据库的 CONTROL 权限授予用户(例如,db_owner 固定数据库角色的成员)后,该用户就可授予对该数据库中任何一个安全对象的任意权限。Grantees of CONTROL permission on a database, such as members of the db_owner fixed database role, can grant any permission on any securable in the database. 被授权 CONTROL 权限的用户可以授予对相应架构中任一个对象的任意权限。Grantees of CONTROL permission on a schema can grant any permission on any object within the schema.

示例Examples

下表列出了安全对象以及描述特定于安全对象的语法的文章。The following table lists the securables and the articles that describe the securable-specific syntax.

应用程序角色Application Role GRANT 数据库主体权限 (Transact-SQL)GRANT Database Principal Permissions (Transact-SQL)
AssemblyAssembly GRANT 程序集权限 (Transact-SQL)GRANT Assembly Permissions (Transact-SQL)
非对称密钥Asymmetric Key GRANT 非对称密钥权限 (Transact-SQL)GRANT Asymmetric Key Permissions (Transact-SQL)
可用性组Availability Group GRANT 可用性组权限 (Transact-SQL)GRANT Availability Group Permissions (Transact-SQL)
证书Certificate GRANT 证书权限 (Transact-SQL)GRANT Certificate Permissions (Transact-SQL)
约定Contract GRANT Service Broker 权限 (Transact-SQL)GRANT Service Broker Permissions (Transact-SQL)
“数据库”Database GRANT 数据库权限 (Transact-SQL)GRANT Database Permissions (Transact-SQL)
数据库作用域凭据Database Scoped Credential GRANT 数据库作用域凭据 (Transact-SQL)GRANT Database Scoped Credential (Transact-SQL)
端点Endpoint GRANT 终结点权限 (Transact-SQL)GRANT Endpoint Permissions (Transact-SQL)
全文目录Full-Text Catalog GRANT 全文权限 (Transact-SQL)GRANT Full-Text Permissions (Transact-SQL)
全文非索引字表Full-Text Stoplist GRANT 全文权限 (Transact-SQL)GRANT Full-Text Permissions (Transact-SQL)
函数Function GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
登录Login GRANT 服务器主体权限 (Transact-SQL)GRANT Server Principal Permissions (Transact-SQL)
消息类型Message Type GRANT Service Broker 权限 (Transact-SQL)GRANT Service Broker Permissions (Transact-SQL)
ObjectObject GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
队列Queue GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
远程服务绑定Remote Service Binding GRANT Service Broker 权限 (Transact-SQL)GRANT Service Broker Permissions (Transact-SQL)
角色Role GRANT 数据库主体权限 (Transact-SQL)GRANT Database Principal Permissions (Transact-SQL)
路由Route GRANT Service Broker 权限 (Transact-SQL)GRANT Service Broker Permissions (Transact-SQL)
架构Schema GRANT 架构权限 (Transact-SQL)GRANT Schema Permissions (Transact-SQL)
搜索属性列表Search Property List GRANT 搜索属性列表权限 (Transact-SQL)GRANT Search Property List Permissions (Transact-SQL)
“服务器”Server GRANT 服务器权限 (Transact-SQL)GRANT Server Permissions (Transact-SQL)
服务Service GRANT Service Broker 权限 (Transact-SQL)GRANT Service Broker Permissions (Transact-SQL)
存储过程Stored Procedure GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
对称密钥Symmetric Key GRANT 对称密钥权限 (Transact-SQL)GRANT Symmetric Key Permissions (Transact-SQL)
同义词Synonym GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
系统对象System Objects GRANT 系统对象权限 (Transact-SQL)GRANT System Object Permissions (Transact-SQL)
Table GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
类型Type GRANT 类型权限 (Transact-SQL)GRANT Type Permissions (Transact-SQL)
用户User GRANT 数据库主体权限 (Transact-SQL)GRANT Database Principal Permissions (Transact-SQL)
“查看”View GRANT 对象权限 (Transact-SQL)GRANT Object Permissions (Transact-SQL)
XML 架构集合XML Schema Collection GRANT XML 架构集合权限 (Transact-SQL)GRANT XML Schema Collection Permissions (Transact-SQL)

另请参阅See Also

DENY (Transact-SQL) DENY (Transact-SQL)
REVOKE (Transact-SQL) REVOKE (Transact-SQL)
sp_addlogin (Transact-SQL) sp_addlogin (Transact-SQL)
sp_adduser (Transact-SQL) sp_adduser (Transact-SQL)
sp_changedbowner (Transact-SQL) sp_changedbowner (Transact-SQL)
sp_dropuser (Transact-SQL) sp_dropuser (Transact-SQL)
sp_helprotect (Transact-SQL) sp_helprotect (Transact-SQL)
sp_helpuser (Transact-SQL)sp_helpuser (Transact-SQL)