--- title: "REVOKE Database Permissions (Transact-SQL)" description: REVOKE Database Permissions (Transact-SQL) author: VanMSFT ms.author: vanto ms.date: "08/10/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, synapse-analytics, pdw" ms.technology: t-sql ms.topic: reference helpviewer_keywords: - "permissions [SQL Server], databases" - "database permissions [SQL Server], revoking" - "REVOKE statement, databases" dev_langs: - "TSQL" monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # REVOKE Database Permissions (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)] Revokes permissions granted and denied on a database. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```syntaxsql REVOKE [ GRANT OPTION FOR ] [ ,...n ] { TO | FROM } [ ,...n ] [ CASCADE ] [ AS ] ::= permission | ALL [ PRIVILEGES ] ::= 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 ``` [!INCLUDE[sql-server-tsql-previous-offline-documentation](../../includes/sql-server-tsql-previous-offline-documentation.md)] ## Arguments *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. > [!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. > [!CAUTION] > A cascaded revocation of a permission granted WITH GRANT OPTION will revoke both GRANT and DENY of that permission. AS \ 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**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later, [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)] Specifies an application role. *Database_user_mapped_to_Windows_User* **Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later Specifies a database user mapped to a Windows user. *Database_user_mapped_to_Windows_Group* **Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later Specifies a database user mapped to a Windows group. *Database_user_mapped_to_certificate* **Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later Specifies a database user mapped to a certificate. *Database_user_mapped_to_asymmetric_key* **Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later Specifies a database user mapped to an asymmetric key. *Database_user_with_no_login* Specifies a database user with no corresponding server-level principal. ## Remarks 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 permission|Implied by database permission|Implied by server permission| |-------------------------|------------------------------------|----------------------------------| |ADMINISTER DATABASE BULK OPERATIONS
**Applies to:** [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].|CONTROL|CONTROL SERVER| |ALTER|CONTROL|ALTER ANY DATABASE| |ALTER ANY APPLICATION ROLE|ALTER|CONTROL SERVER| |ALTER ANY ASSEMBLY|ALTER|CONTROL SERVER| |ALTER ANY ASYMMETRIC KEY|ALTER|CONTROL SERVER| |ALTER ANY CERTIFICATE|ALTER|CONTROL SERVER| |ALTER ANY COLUMN ENCRYPTION KEY|ALTER|CONTROL SERVER| |ALTER ANY COLUMN MASTER KEY DEFINITION|ALTER|CONTROL SERVER| |ALTER ANY CONTRACT|ALTER|CONTROL SERVER| |ALTER ANY DATABASE AUDIT|ALTER|ALTER ANY SERVER AUDIT| |ALTER ANY DATABASE DDL TRIGGER|ALTER|CONTROL SERVER| |ALTER ANY DATABASE EVENT NOTIFICATION|ALTER|ALTER ANY EVENT NOTIFICATION| |ALTER ANY DATABASE EVENT SESSION
**Applies to**: [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)].|ALTER|ALTER ANY EVENT SESSION| |ALTER ANY DATABASE SCOPED CONFIGURATION
**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS](../../includes/sssds-md.md)].|CONTROL|CONTROL SERVER| |ALTER ANY DATASPACE|ALTER|CONTROL SERVER| |ALTER ANY EXTERNAL DATA SOURCE|ALTER|CONTROL SERVER| |ALTER ANY EXTERNAL FILE FORMAT|ALTER|CONTROL SERVER| |ALTER ANY EXTERNAL LIBRARY
**Applies to**: [!INCLUDE [sssql17-md](../../includes/sssql17-md.md)].|CONTROL|CONTROL SERVER | |ALTER ANY FULLTEXT CATALOG|ALTER|CONTROL SERVER| |ALTER ANY MASK|CONTROL|CONTROL SERVER| |ALTER ANY MESSAGE TYPE|ALTER|CONTROL SERVER| |ALTER ANY REMOTE SERVICE BINDING|ALTER|CONTROL SERVER| |ALTER ANY ROLE|ALTER|CONTROL SERVER| |ALTER ANY ROUTE|ALTER|CONTROL SERVER| |ALTER ANY SCHEMA|ALTER|CONTROL SERVER| |ALTER ANY SECURITY POLICY
**Applies to**: [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)].|CONTROL|CONTROL SERVER| |ALTER ANY SERVICE|ALTER|CONTROL SERVER| |ALTER ANY SYMMETRIC KEY|ALTER|CONTROL SERVER| |ALTER ANY USER|ALTER|CONTROL SERVER| |AUTHENTICATE|CONTROL|AUTHENTICATE SERVER| |BACKUP DATABASE|CONTROL|CONTROL SERVER| |BACKUP LOG|CONTROL|CONTROL SERVER| |CHECKPOINT|CONTROL|CONTROL SERVER| |CONNECT|CONNECT REPLICATION|CONTROL SERVER| |CONNECT REPLICATION|CONTROL|CONTROL SERVER| |CONTROL|CONTROL|CONTROL SERVER| |CREATE AGGREGATE|ALTER|CONTROL SERVER| |CREATE ASSEMBLY|ALTER ANY ASSEMBLY|CONTROL SERVER| |CREATE ASYMMETRIC KEY|ALTER ANY ASYMMETRIC KEY|CONTROL SERVER| |CREATE CERTIFICATE|ALTER ANY CERTIFICATE|CONTROL SERVER| |CREATE CONTRACT|ALTER ANY CONTRACT|CONTROL SERVER| |CREATE DATABASE|CONTROL|CREATE ANY DATABASE| |CREATE DATABASE DDL EVENT NOTIFICATION|ALTER ANY DATABASE EVENT NOTIFICATION|CREATE DDL EVENT NOTIFICATION| |CREATE DEFAULT|ALTER|CONTROL SERVER| |CREATE FULLTEXT CATALOG|ALTER ANY FULLTEXT CATALOG|CONTROL SERVER| |CREATE FUNCTION|ALTER|CONTROL SERVER| |CREATE MESSAGE TYPE|ALTER ANY MESSAGE TYPE|CONTROL SERVER| |CREATE PROCEDURE|ALTER|CONTROL SERVER| |CREATE QUEUE|ALTER|CONTROL SERVER| |CREATE REMOTE SERVICE BINDING|ALTER ANY REMOTE SERVICE BINDING|CONTROL SERVER| |CREATE ROLE|ALTER ANY ROLE|CONTROL SERVER| |CREATE ROUTE|ALTER ANY ROUTE|CONTROL SERVER| |CREATE RULE|ALTER|CONTROL SERVER| |CREATE SCHEMA|ALTER ANY SCHEMA|CONTROL SERVER| |CREATE SERVICE|ALTER ANY SERVICE|CONTROL SERVER| |CREATE SYMMETRIC KEY|ALTER ANY SYMMETRIC KEY|CONTROL SERVER| |CREATE SYNONYM|ALTER|CONTROL SERVER| |CREATE TABLE|ALTER|CONTROL SERVER| |CREATE TYPE|ALTER|CONTROL SERVER| |CREATE VIEW|ALTER|CONTROL SERVER| |CREATE XML SCHEMA COLLECTION|ALTER|CONTROL SERVER| |DELETE|CONTROL|CONTROL SERVER| |EXECUTE|CONTROL|CONTROL SERVER| |EXECUTE ANY EXTERNAL SCRIPT
**Applies to**: [!INCLUDE[ssSQL15_md](../../includes/sssql16-md.md)].|CONTROL|CONTROL SERVER| |INSERT|CONTROL|CONTROL SERVER| |KILL DATABASE CONNECTION
**Applies to**: [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)].|CONTROL|ALTER ANY CONNECTION| |REFERENCES|CONTROL|CONTROL SERVER| |SELECT|CONTROL|CONTROL SERVER| |SHOWPLAN|CONTROL|ALTER TRACE| |SUBSCRIBE QUERY NOTIFICATIONS|CONTROL|CONTROL SERVER| |TAKE OWNERSHIP|CONTROL|CONTROL SERVER| |UNMASK|CONTROL|CONTROL SERVER| |UPDATE|CONTROL|CONTROL SERVER| |VIEW ANY COLUMN ENCRYPTION KEY DEFINITION|CONTROL|VIEW ANY DEFINITION| |VIEW ANY COLUMN MASTER KEY DEFINITION|CONTROL|VIEW ANY DEFINITION| |VIEW DATABASE STATE|CONTROL|VIEW SERVER STATE| |VIEW DEFINITION|CONTROL|VIEW ANY DEFINITION| ## Permissions 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. ## Examples ### A. Revoking permission to create certificates The following example revokes `CREATE CERTIFICATE` permission on the `AdventureWorks2012` database from user `MelanieK`. **Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later ```sql 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**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] and later, [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)] ```sql 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. ```sql USE AdventureWorks2012; REVOKE VIEW DEFINITION FROM CarmineEs CASCADE; GO ``` ## See Also [sys.database_permissions (Transact-SQL)](../../relational-databases/system-catalog-views/sys-database-permissions-transact-sql.md) [sys.database_principals (Transact-SQL)](../../relational-databases/system-catalog-views/sys-database-principals-transact-sql.md) [GRANT Database Permissions (Transact-SQL)](../../t-sql/statements/grant-database-permissions-transact-sql.md) [DENY Database Permissions (Transact-SQL)](../../t-sql/statements/deny-database-permissions-transact-sql.md) [Permissions (Database Engine)](../../relational-databases/security/permissions-database-engine.md) [Principals (Database Engine)](../../relational-databases/security/authentication-access/principals-database-engine.md)