REVOKE (Transact-SQL)REVOKE (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

撤消以前授予或拒绝的权限。Removes a previously granted or denied permission.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

GRANT OPTION FORGRANT OPTION FOR
指示将撤消授予指定权限的能力。Indicates that the ability to grant the specified permission will be revoked. 在使用 CASCADE 参数时,需要具备该功能。This is required when you are using the CASCADE argument.

重要

如果主体具有不带 GRANT 选项的指定权限,则将撤消该权限本身。If the principal has the specified permission without the GRANT option, the permission itself will be revoked.

ALLALL
适用于SQL Server 2008SQL Server 2008 及更高版本Applies to: SQL Server 2008SQL Server 2008 and later

该选项不会撤消所有可能的权限。This option does not revoke all possible permissions. 撤消 ALL 相当于撤消以下权限。Revoking ALL is equivalent to revoking 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.

备注

不推荐使用 REVOKE ALL 语法。The REVOKE ALL syntax is deprecated. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.应改为撤销特定权限。Revoke specific permissions instead.

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 topics listed in Securable-specific Syntax later in this topic.

columncolumn
指定表中将撤消其权限的列的名称。Specifies the name of a column in a table on which permissions are being revoked. 需要使用括号。The parentheses are required.

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

securable securable
指定将撤消其权限的安全对象。Specifies the securable on which the permission is being revoked.

TO | FROM principal TO | FROM principal
主体的名称。Is the name of a principal. 可撤消其对安全对象的权限的主体随安全对象而异。The principals from which permissions on a securable can be revoked vary, depending on the securable. 有关有效组合的详细信息,请参阅本主题后面的特定于安全对象的语法部分所列出的主题。For more information about valid combinations, see the topics listed in Securable-specific Syntax later in this topic.

CASCADECASCADE
指示当前正在撤消的权限也将从其他被该主体授权的主体中撤消。Indicates that the permission that is being revoked is also revoked from other principals to which it has been granted by this principal. 使用 CASCADE 参数时,还必须同时指定 GRANT OPTION FOR 参数。When you are using the CASCADE argument, you must also include the GRANT OPTION FOR argument.

注意

如果对授予了 WITH GRANT OPTION 权限的权限执行级联撤消,将同时撤消该权限的 GRANT 和 DENY 权限。A cascaded revocation of a permission granted WITH GRANT OPTION will revoke both GRANT and DENY of that permission.

AS principal AS principal
使用 AS principal 子句指示将撤销由你以外的主体所授予的权限。Use the AS principal clause to indicate that you are revoking a permission that was granted by a principal other than you. 例如,假设用户 Mary 是 principal_id 12,用户 Raul 是主体 15。For example, presume that user Mary is principal_id 12 and user Raul is principal 15. Mary 和 Raul 为用户 Steven 授予相同权限。Both Mary and Raul grant a user named Steven the same permission. Sys.database_permissions 表两次指示该权限,但分别具有不同的 grantor_prinicpal_id 值。The sys.database_permissions table will indicate the permissions twice but they will each have a different grantor_prinicpal_id value. Mary 可以使用 AS RAUL 子句撤销权限以移除 Raul 授予的权限。Mary could revoke the permission using the AS RAUL clause to remove Raul's grant of the permission.

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

RemarksRemarks

REVOKE 语句的完整语法非常复杂。The full syntax of the REVOKE statement is complex. 上面的语法关系图进行了简化以突出其结构。The syntax diagram above was simplified to draw attention to its structure. 本主题后面的特定于安全对象的语法部分中列出的主题介绍了用于撤销特定安全对象的权限的完整语句。Complete syntax for revoking permissions on specific securables is described in the topics listed in Securable-specific Syntax later in this topic.

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. 但是,在更高级别撤销已授予权限的操作并不优先。However, revoking the granted permission at a higher scope does not take precedence.

注意

表级 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 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

在撤消通过指定 GRANT OPTION 为其赋予权限的主体的权限时,如果未指定 CASCADE,则将无法成功执行 REVOKE 语句。The REVOKE statement will fail if CASCADE is not specified when you are revoking a permission from a principal that was granted that permission with GRANT OPTION specified.

权限Permissions

对安全对象具有 CONTROL 权限的主体可以撤消该安全对象的权限。Principals with CONTROL permission on a securable can revoke permission on that securable. 对象所有者可以撤消他们所拥有的对象的权限。Object owners can revoke permissions on the objects they own.

具备 CONTROL SERVER 权限的被权限者(例如 sysadmin 固定服务器角色的成员)可以撤消对该服务器的任何安全对象所拥有的任何权限。Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can revoke 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 revoke any permission on any securable in the database. 被授予架构的 CONTROL 权限的用户可以撤消针对该架构的任何对象的任何权限。Grantees of CONTROL permission on a schema can revoke any permission on any object within the schema.

特定于安全对象的语法Securable-specific Syntax

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

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

另请参阅See Also

权限层次结构(数据库引擎) Permissions Hierarchy (Database Engine)
DENY (Transact-SQL) DENY (Transact-SQL)
GRANT (Transact-SQL) GRANT (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)