DENY Database Permissions (Transact-SQL)

 

Updated: November 30, 2016

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

Denies permissions on a database in SQL Server.

Topic link icon Transact-SQL Syntax Conventions

  
DENY <permission> [ ,...n ]    
    TO <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 deny all possible permissions. Denying ALL is equivalent to denying 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.

CASCADE
Indicates that the permission will also be denied to principals to which the specified principal granted it.

AS <database_principal>
Specifies a principal from which the principal executing this query derives its right to deny 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.

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.

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 denied 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
ADMINISTER DATABASE BULK OPERATIONS
Applies to: SQL Database.
CONTROLCONTROL SERVER
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
 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 SECURITY POLICY
 Applies to: Azure SQL Database.
CONTROLCONTROL SERVER
ALTER ANY SCHEMAALTERCONTROL 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 KEYCONTROLVIEW ANY DEFINITION
VIEW ANY 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. Denying permission to create certificates

The following example denies CREATE CERTIFICATE permission on the AdventureWorks2012 database to user MelanieK.

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

B. Denying REFERENCES permission to an application role

The following example denies REFERENCES permission on the AdventureWorks2012 database to application role AuditMonitor.

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database.
USE AdventureWorks2012;  
DENY REFERENCES TO AuditMonitor;  
GO  

C. Denying VIEW DEFINITION with CASCADE

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

USE AdventureWorks2012;  
DENY VIEW DEFINITION TO CarmineEs CASCADE;  
GO  

sys.database_permissions (Transact-SQL)
sys.database_principals (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL)
GRANT (Transact-SQL)
Permissions (Database Engine)
Principals (Database Engine)

Community Additions

ADD
Show: