REVOKE Database Permissions (Transact-SQL)

 

Updated: March 11, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Revokes permissions granted and denied on a database.

Topic link icon Transact-SQL Syntax Conventions

  
REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ]    
    { TO | FROM } <database_principal> [ ,...n ]   
        [ CASCADE ]  
    [ AS <database_principal> ]  
  
<permission> ::=    
permission | ALL [ PRIVILEGES ]  
  
<database_principal> ::=   
      Database_user   
    | Database_role   
    | Application_role   
    | Database_user_mapped_to_Windows_User   
    | Database_user_mapped_to_Windows_Group   
    | Database_user_mapped_to_certificate   
    | Database_user_mapped_to_asymmetric_key   
    | Database_user_with_no_login    

permission
Specifies a permission that can be denied on a database. For a list of the permissions, see the Remarks section later in this topic.

ALL
This option does not revoke all possible permissions. Revoking ALL is equivalent to revoking the following permissions: BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.

PRIVILEGES
Included for ISO compliance. Does not change the behavior of ALL.

GRANT OPTION
Indicates that the right to grant the specified permission to other principals will be revoked. The permission itself will not be revoked.

System_CAPS_ICON_important.jpg Important


If the principal has the specified permission without the GRANT option, the permission itself will be revoked.

CASCADE
Indicates that the permission being revoked is also revoked from other principals to which it has been granted or denied by this principal.

System_CAPS_ICON_caution.jpg Caution


A cascaded revocation of a permission granted WITH GRANT OPTION will revoke both GRANT and DENY of that permission.

AS <database_principal>
Specifies a principal from which the principal executing this query derives its right to revoke the permission.

Database_user
Specifies a database user.

Database_role
Specifies a database role.

Application_role

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.

Specifies an application role.

Database_user_mapped_to_Windows_User

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a Windows user.

Database_user_mapped_to_Windows_Group

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a Windows group.

Database_user_mapped_to_certificate

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a certificate.

Database_user_mapped_to_asymmetric_key

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to an asymmetric key.

Database_user_with_no_login
Specifies a database user with no corresponding server-level principal.

The statement will fail if CASCADE is not specified when you are revoking a permission to a principal that was granted that permission with the GRANT OPTION specified.

A database is a securable contained by the server that is its parent in the permissions hierarchy. The most specific and limited permissions that can be revoked on a database are listed in the following table, together with the more general permissions that include them by implication.

Database permissionImplied by database permissionImplied by server permission
ALTERCONTROLALTER ANY DATABASE
ALTER ANY APPLICATION ROLEALTERCONTROL SERVER
ALTER ANY ASSEMBLYALTERCONTROL SERVER
ALTER ANY ASYMMETRIC KEYALTERCONTROL SERVER
ALTER ANY CERTIFICATEALTERCONTROL SERVER
ALTER ANY COLUMN ENCRYPTION KEYALTERCONTROL SERVER
ALTER ANY COLUMN MASTER KEY DEFINITIONALTERCONTROL SERVER
ALTER ANY CONTRACTALTERCONTROL SERVER
ALTER ANY DATABASE AUDITALTERALTER ANY SERVER AUDIT
ALTER ANY DATABASE DDL TRIGGERALTERCONTROL SERVER
ALTER ANY DATABASE EVENT NOTIFICATIONALTERALTER ANY EVENT NOTIFICATION
ALTER ANY DATABASE EVENT SESSION

 Applies to: Azure SQL Database.
ALTERALTER ANY EVENT SESSION
ALTER ANY DATABASE SCOPED CONFIGURATION

 Applies to: SQL Server 2016 through SQL Server 2016, SQL Database.
CONTROLCONTROL SERVER
ALTER ANY DATASPACEALTERCONTROL SERVER
ALTER ANY EXTERNAL DATA SOURCEALTERCONTROL SERVER
ALTER ANY EXTERNAL FILE FORMATALTERCONTROL SERVER
ALTER ANY FULLTEXT CATALOGALTERCONTROL SERVER
ALTER ANY MASKCONTROLCONTROL SERVER
ALTER ANY MESSAGE TYPEALTERCONTROL SERVER
ALTER ANY REMOTE SERVICE BINDINGALTERCONTROL SERVER
ALTER ANY ROLEALTERCONTROL SERVER
ALTER ANY ROUTEALTERCONTROL SERVER
ALTER ANY SCHEMAALTERCONTROL SERVER
ALTER ANY SECURITY POLICY

 Applies to: Azure SQL Database.
CONTROLCONTROL SERVER
ALTER ANY SERVICEALTERCONTROL SERVER
ALTER ANY SYMMETRIC KEYALTERCONTROL SERVER
ALTER ANY USERALTERCONTROL SERVER
AUTHENTICATECONTROLAUTHENTICATE SERVER
BACKUP DATABASECONTROLCONTROL SERVER
BACKUP LOGCONTROLCONTROL SERVER
CHECKPOINTCONTROLCONTROL SERVER
CONNECTCONNECT REPLICATIONCONTROL SERVER
CONNECT REPLICATIONCONTROLCONTROL SERVER
CONTROLCONTROLCONTROL SERVER
CREATE AGGREGATEALTERCONTROL SERVER
CREATE ASSEMBLYALTER ANY ASSEMBLYCONTROL SERVER
CREATE ASYMMETRIC KEYALTER ANY ASYMMETRIC KEYCONTROL SERVER
CREATE CERTIFICATEALTER ANY CERTIFICATECONTROL SERVER
CREATE CONTRACTALTER ANY CONTRACTCONTROL SERVER
CREATE DATABASECONTROLCREATE ANY DATABASE
CREATE DATABASE DDL EVENT NOTIFICATIONALTER ANY DATABASE EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATION
CREATE DEFAULTALTERCONTROL SERVER
CREATE FULLTEXT CATALOGALTER ANY FULLTEXT CATALOGCONTROL SERVER
CREATE FUNCTIONALTERCONTROL SERVER
CREATE MESSAGE TYPEALTER ANY MESSAGE TYPECONTROL SERVER
CREATE PROCEDUREALTERCONTROL SERVER
CREATE QUEUEALTERCONTROL SERVER
CREATE REMOTE SERVICE BINDINGALTER ANY REMOTE SERVICE BINDINGCONTROL SERVER
CREATE ROLEALTER ANY ROLECONTROL SERVER
CREATE ROUTEALTER ANY ROUTECONTROL SERVER
CREATE RULEALTERCONTROL SERVER
CREATE SCHEMAALTER ANY SCHEMACONTROL SERVER
CREATE SERVICEALTER ANY SERVICECONTROL SERVER
CREATE SYMMETRIC KEYALTER ANY SYMMETRIC KEYCONTROL SERVER
CREATE SYNONYMALTERCONTROL SERVER
CREATE TABLEALTERCONTROL SERVER
CREATE TYPEALTERCONTROL SERVER
CREATE VIEWALTERCONTROL SERVER
CREATE XML SCHEMA COLLECTIONALTERCONTROL SERVER
DELETECONTROLCONTROL SERVER
EXECUTECONTROLCONTROL SERVER
INSERTCONTROLCONTROL SERVER
KILL DATABASE CONNECTION

 Applies to: Azure SQL Database.
CONTROLALTER ANY CONNECTION
REFERENCESCONTROLCONTROL SERVER
SELECTCONTROLCONTROL SERVER
SHOWPLANCONTROLALTER TRACE
SUBSCRIBE QUERY NOTIFICATIONSCONTROLCONTROL SERVER
TAKE OWNERSHIPCONTROLCONTROL SERVER
UNMASKCONTROLCONTROL SERVER
UPDATECONTROLCONTROL SERVER
VIEW ANY COLUMN ENCRYPTION KEY DEFINITIONCONTROLVIEW ANY DEFINITION
VIEW ANY COLUMN MASTER KEY DEFINITIONCONTROLVIEW ANY DEFINITION
VIEW DATABASE STATECONTROLVIEW SERVER STATE
VIEW DEFINITIONCONTROLVIEW ANY DEFINITION

The principal that executes this statement (or the principal specified with the AS option) must have CONTROL permission on the database or a higher permission that implies CONTROL permission on the database.

If you are using the AS option, the specified principal must own the database.

A. Revoking permission to create certificates

The following example revokes CREATE CERTIFICATE permission on the AdventureWorks2012 database from user MelanieK.

Applies to: SQL Server 2008 through SQL Server 2016.
USE AdventureWorks2012;  
REVOKE CREATE CERTIFICATE FROM MelanieK;  
GO  

B. Revoking REFERENCES permission from an application role

The following example revokes REFERENCES permission on the AdventureWorks2012 database from application role AuditMonitor.

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.
USE AdventureWorks2012;  
REVOKE REFERENCES FROM AuditMonitor;  
GO  

C. Revoking VIEW DEFINITION with CASCADE

The following example revokes VIEW DEFINITION permission on the AdventureWorks2012 database from user CarmineEs and from all principals to which CarmineEs has granted VIEW DEFINITION permission.

USE AdventureWorks2012;  
REVOKE VIEW DEFINITION FROM CarmineEs CASCADE;  
GO  

sys.database_permissions (Transact-SQL)
sys.database_principals (Transact-SQL)
GRANT Database Permissions (Transact-SQL)
DENY Database Permissions (Transact-SQL)
Permissions (Database Engine)
Principals (Database Engine)

Community Additions

ADD
Show: