REVOKE 服务器权限 (Transact-SQL)REVOKE Server Permissions (Transact-SQL)

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

删除服务器级 GRANT 和 DENY 权限。Removes server-level GRANT and DENY permissions.

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

语法Syntax

  
REVOKE [ GRANT OPTION FOR ] permission  [ ,...n ]   
    { TO | FROM } <grantee_principal> [ ,...n ]  
        [ CASCADE ]  
    [ AS <grantor_principal> ]   
  
<grantee_principal> ::= SQL_Server_login   
        | SQL_Server_login_mapped_to_Windows_login  
    | SQL_Server_login_mapped_to_Windows_group  
    | SQL_Server_login_mapped_to_certificate  
    | SQL_Server_login_mapped_to_asymmetric_key  
    | server_role  
  
<grantor_principal> ::= SQL_Server_login   
    | SQL_Server_login_mapped_to_Windows_login  
    | SQL_Server_login_mapped_to_Windows_group  
    | SQL_Server_login_mapped_to_certificate  
    | SQL_Server_login_mapped_to_asymmetric_key  
    | server_role  

参数Arguments

permission permission
指定可对服务器授予的权限。Specifies a permission that can be granted on a server. 有关权限的列表,请参阅本主题后面的“备注”部分。For a list of the permissions, see the Remarks section later in this topic.

{ TO | FROM } <grantee_principal> 指定要从中撤消权限的主体。{ TO | FROM } <grantee_principal> Specifies the principal from which the permission is being revoked.

AS <grantor_principal> 指定一个主体,执行此查询的主体从该主体获得撤消权限的权利。AS <grantor_principal> Specifies the principal from which the principal executing this query derives its right to revoke the permission.

GRANT OPTION FORGRANT OPTION FOR
指示要撤消向其他主体授予指定权限的权限。Indicates that the right to grant the specified permission to other principals will be revoked. 不会撤消该权限本身。The permission itself will not be revoked.

重要

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

CASCADECASCADE
指示要撤消的权限也会从此主体授予或拒绝该权限的其他主体中撤消。Indicates that the permission being revoked is also revoked from other principals to which it has been granted or denied by this principal.

注意

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

SQL_Server_login SQL_Server_login
指定 SQL ServerSQL Server 登录名。Specifies a SQL ServerSQL Server login.

SQL_Server_login_mapped_to_Windows_login SQL_Server_login_mapped_to_Windows_login
指定映射到 Windows 登录名的 SQL ServerSQL Server 登录名。Specifies a SQL ServerSQL Server login mapped to a Windows login.

SQL_Server_login_mapped_to_Windows_group SQL_Server_login_mapped_to_Windows_group
指定映射到 Windows 组的 SQL ServerSQL Server 登录名。Specifies a SQL ServerSQL Server login mapped to a Windows group.

SQL_Server_login_mapped_to_certificate SQL_Server_login_mapped_to_certificate
指定映射到证书的 SQL ServerSQL Server 登录名。Specifies a SQL ServerSQL Server login mapped to a certificate.

SQL_Server_login_mapped_to_asymmetric_key SQL_Server_login_mapped_to_asymmetric_key
指定映射到非对称密钥的 SQL ServerSQL Server 登录名。Specifies a SQL ServerSQL Server login mapped to an asymmetric key.

server_role server_role
指定用户定义的服务器角色。Specifies a user-defined server role.

备注Remarks

只有在当前数据库为 master 时,才可撤消其服务器作用域内的权限。Permissions at the server scope can be revoked only when the current database is master.

REVOKE 操作可同时删除 GRANT 和 DENY 权限。REVOKE removes both GRANT and DENY permissions.

可以使用 REVOKE GRANT OPTION FOR 撤消重新授予指定权限的权限。Use REVOKE GRANT OPTION FOR to revoke the right to regrant the specified permission. 如果主体所具有的权限还带有授予该权限的权限,则将撤消授予该权限的权限,而不会撤消权限本身。If the principal has the permission with the right to grant it, the right to grant the permission will be revoked, and the permission itself will not be revoked. 但是,如果主体具有不带 GRANT 选项的指定权限,则会撤消权限本身。But if the principal has the specified permission without the GRANT option, the permission itself will be revoked.

可以在 sys.server_permissions 目录视图中查看有关服务器权限的信息;在 sys.server_principals 目录视图中查看有关服务器主体的信息。Information about server permissions can be viewed in the sys.server_permissions catalog view, and information about server principals can be viewed in the sys.server_principals catalog view. 以及在 sys.server_role_members 目录视图中查看有关服务器角色成员身份的信息。Information about membership of server roles can be viewed in the sys.server_role_members catalog view.

服务器是权限层次结构的最高级别。A server is the highest level of the permissions hierarchy. 下表列出了可撤消的对服务器最为具体的限定权限。The most specific and limited permissions that can be revoked on a server are listed in the following table.

服务器权限Server permission 服务器权限隐含的权限Implied by server permission
ADMINISTER BULK OPERATIONSADMINISTER BULK OPERATIONS CONTROL SERVERCONTROL SERVER
ALTER ANY AVAILABILITY GROUPALTER ANY AVAILABILITY GROUP

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version).
CONTROL SERVERCONTROL SERVER
ALTER ANY CONNECTIONALTER ANY CONNECTION CONTROL SERVERCONTROL SERVER
ALTER ANY CREDENTIALALTER ANY CREDENTIAL CONTROL SERVERCONTROL SERVER
ALTER ANY DATABASEALTER ANY DATABASE CONTROL SERVERCONTROL SERVER
ALTER ANY ENDPOINTALTER ANY ENDPOINT CONTROL SERVERCONTROL SERVER
ALTER ANY EVENT NOTIFICATIONALTER ANY EVENT NOTIFICATION CONTROL SERVERCONTROL SERVER
ALTER ANY EVENT SESSIONALTER ANY EVENT SESSION CONTROL SERVERCONTROL SERVER
ALTER ANY LINKED SERVERALTER ANY LINKED SERVER CONTROL SERVERCONTROL SERVER
ALTER ANY LOGINALTER ANY LOGIN CONTROL SERVERCONTROL SERVER
ALTER ANY SERVER AUDITALTER ANY SERVER AUDIT CONTROL SERVERCONTROL SERVER
ALTER ANY SERVER ROLEALTER ANY SERVER ROLE

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version).
CONTROL SERVERCONTROL SERVER
ALTER RESOURCESALTER RESOURCES CONTROL SERVERCONTROL SERVER
ALTER SERVER STATEALTER SERVER STATE CONTROL SERVERCONTROL SERVER
ALTER SETTINGSALTER SETTINGS CONTROL SERVERCONTROL SERVER
ALTER TRACEALTER TRACE CONTROL SERVERCONTROL SERVER
AUTHENTICATE SERVERAUTHENTICATE SERVER CONTROL SERVERCONTROL SERVER
CONNECT ANY DATABASECONNECT ANY DATABASE

适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version).
CONTROL SERVERCONTROL SERVER
CONNECT SQLCONNECT SQL CONTROL SERVERCONTROL SERVER
CONTROL SERVERCONTROL SERVER CONTROL SERVERCONTROL SERVER
CREATE ANY DATABASECREATE ANY DATABASE ALTER ANY DATABASEALTER ANY DATABASE
CREATE AVAILABILITY GROUPCREATE AVAILABILITY GROUP

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version).
ALTER ANY AVAILABILITY GROUPALTER ANY AVAILABILITY GROUP
CREATE DDL EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATION ALTER ANY EVENT NOTIFICATIONALTER ANY EVENT NOTIFICATION
CREATE ENDPOINTCREATE ENDPOINT ALTER ANY ENDPOINTALTER ANY ENDPOINT
CREATE SERVER ROLECREATE SERVER ROLE

适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through current version).
ALTER ANY SERVER ROLEALTER ANY SERVER ROLE
CREATE TRACE EVENT NOTIFICATIONCREATE TRACE EVENT NOTIFICATION ALTER ANY EVENT NOTIFICATIONALTER ANY EVENT NOTIFICATION
EXTERNAL ACCESS ASSEMBLYEXTERNAL ACCESS ASSEMBLY CONTROL SERVERCONTROL SERVER
IMPERSONATE ANY LOGINIMPERSONATE ANY LOGIN

适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version).
CONTROL SERVERCONTROL SERVER
SELECT ALL USER SECURABLESSELECT ALL USER SECURABLES

适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)当前版本)。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version).
CONTROL SERVERCONTROL SERVER
关机SHUTDOWN CONTROL SERVERCONTROL SERVER
UNSAFE ASSEMBLYUNSAFE ASSEMBLY CONTROL SERVERCONTROL SERVER
VIEW ANY DATABASEVIEW ANY DATABASE VIEW ANY DEFINITIONVIEW ANY DEFINITION
VIEW ANY DEFINITIONVIEW ANY DEFINITION CONTROL SERVERCONTROL SERVER
VIEW SERVER STATEVIEW SERVER STATE ALTER SERVER STATEALTER SERVER STATE

权限Permissions

要求具有 CONTROL SERVER 权限,或者具有 sysadmin 固定服务器角色的成员身份。Requires CONTROL SERVER permission or membership in the sysadmin fixed server role.

示例Examples

A.A. 从登录名中撤消权限Revoking a permission from a login

以下示例从 VIEW SERVER STATE 登录名 SQL ServerSQL Server 中撤消 WanidaBenshoof 权限。The following example revokes VIEW SERVER STATE permission from the SQL ServerSQL Server login WanidaBenshoof.

USE master;  
REVOKE VIEW SERVER STATE FROM WanidaBenshoof;  
GO  

B.B. 撤消 WITH GRANT 选项Revoking the WITH GRANT option

以下示例从 CONNECT SQL 登录名 SQL ServerSQL Server 中撤消授予 JanethEsteves 的权限。The following example revokes the right to grant CONNECT SQL from the SQL ServerSQL Server login JanethEsteves.

USE master;  
REVOKE GRANT OPTION FOR CONNECT SQL FROM JanethEsteves;  
GO  

该登录名仍然具有 CONNECT SQL 权限,但是不能将该权限再授予其他主体。The login still has CONNECT SQL permission, but it can no longer grant that permission to other principals.

另请参阅See Also

GRANT (Transact-SQL) GRANT (Transact-SQL)
DENY (Transact-SQL) DENY (Transact-SQL)
DENY 服务器权限 (Transact-SQL) DENY Server Permissions (Transact-SQL)
REVOKE 服务器权限 (Transact-SQL) REVOKE Server Permissions (Transact-SQL)
权限层次结构(数据库引擎) Permissions Hierarchy (Database Engine)
sys.fn_builtin_permissions (Transact-SQL) sys.fn_builtin_permissions (Transact-SQL)
sys.fn_my_permissions (Transact-SQL) sys.fn_my_permissions (Transact-SQL)
HAS_PERMS_BY_NAME (Transact-SQL)HAS_PERMS_BY_NAME (Transact-SQL)