Export (0) Print
Expand All

DENY (Transact-SQL)

Updated: 12 December 2006

Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships.


Simplified syntax for DENY
DENY { ALL [ PRIVILEGES ] }
      | permission [ ( column [ ,...n ] ) ] [ ,...n ]
      [ ON [ class :: ] securable ] TO principal [ ,...n ] 
      [ CASCADE] [ AS principal ]

ALL

This option does not deny all possible permissions. Denying ALL is equivalent to denying the following permissions.

  • 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.
  • If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
  • If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a stored procedure, "ALL" means EXECUTE.
  • If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
PRIVILEGES

Included for SQL-92 compliance. Does not change the behavior of ALL.

permission

The name of a permission. The valid mappings of permissions to securables are described in the sub-topics listed below.

column

Specifies the name of a column in a table on which permissions are being denied. The parentheses "()" are required.

class

Specifies the class of the securable on which the permission is being denied. The scope qualifier "::" is required.

securable

Specifies the securable on which the permission is being denied.

TO principal

The name of a principal. The principals to which permissions on a securable can be denied vary, depending on the securable. See the securable-specific topics listed below for valid combinations.

CASCADE

Indicates that the permission is denied to the specified principal and to all other principals to which the principal granted the permission. Required when the principal has the permission with GRANT OPTION.

AS principal

Specifies a principal from which the principal executing this statement derives its right to deny the permission.

The full syntax of the DENY statement is complex. The diagram above has been simplified to draw attention to its structure. Complete syntax for denying permissions on specific securables is described in the topics listed below.

DENY will fail if CASCADE is not specified when denying permission to a principal that was granted that permission with GRANT OPTION specified.

Column-level permissions take precedence over object permissions. For example, if a deny permission is applied to a base object such as a table and then a grant permission is applied to one of the base object’s columns, the grantee of the permission will have access to the column in spite of the deny on the base object. However, to ensure that column-level permissions are present, they must be applied after permissions on the base object.

ms188338.note(en-US,SQL.90).gifNote:
In SQL Server 2005, this inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.

The sp_helprotect system stored procedure reports permissions on a database-level securable.

ms188338.Caution(en-US,SQL.90).gifCaution:
Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database can not connect to that database. By the same token, denying CONTROL SERVER permission implicitly denies CONNECT SQL permission on the server. A principal that is denied CONTROL SERVER permission on a server can not connect to that server.

The caller (or the principal specified with the AS option) must have either CONTROL permission on the securable, or a higher permission that implies CONTROL permission on the securable. If using the AS option, the specified principal must own the securable on which a permission is being denied.

Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can deny any permission on any securable in the server. Grantees of CONTROL permission on the database, such as members of the db_owner fixed database role, can deny any permission on any securable in the database. Grantees of CONTROL permission on a schema can deny any permission on any object in the schema. If the AS clause is used, the specified principal must own the securable on which permissions are being denied.

See the following topics for securable-specific syntax.

Application Role

DENY Database Principal Permissions (Transact-SQL)

Assembly

DENY Assembly Permissions (Transact-SQL)

Asymmetric Key

DENY Asymmetric Key Permissions (Transact-SQL)

Certificate

DENY Certificate Permissions (Transact-SQL)

Contract

DENY Service Broker Permissions (Transact-SQL)

Database

DENY Database Permissions (Transact-SQL)

Endpoint

DENY Endpoint Permissions (Transact-SQL)

Full-text Catalog

DENY Full-text Catalog Permissions (Transact-SQL)

Function

DENY Object Permissions (Transact-SQL)

Login

DENY Server Principal Permissions (Transact-SQL)

Message Type

DENY Service Broker Permissions (Transact-SQL)

Object

DENY Object Permissions (Transact-SQL)

Queue

DENY Object Permissions (Transact-SQL)

Remote Service Binding

DENY Service Broker Permissions (Transact-SQL)

Role

DENY Database Principal Permissions (Transact-SQL)

Route

DENY Service Broker Permissions (Transact-SQL)

Schema

DENY Schema Permissions (Transact-SQL)

Server

DENY Server Permissions (Transact-SQL)

Service

DENY Service Broker Permissions (Transact-SQL)

Stored Procedure

DENY Object Permissions (Transact-SQL)

Symmetric Key

DENY Symmetric Key Permissions (Transact-SQL)

Synonym

DENY Object Permissions (Transact-SQL)

System Objects

DENY System Object Permissions (Transact-SQL)

Table

DENY Object Permissions (Transact-SQL)

Type

DENY Type Permissions (Transact-SQL)

User

DENY Database Principal Permissions (Transact-SQL)

View

DENY Object Permissions (Transact-SQL)

XML Schema Collection

DENY XML Schema Collection Permissions (Transact-SQL)

Release History

12 December 2006

New content:
  • Added content clarifying precedence of column-level permissions over object permissions.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft