DENY
Creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.
Syntax
Statement permissions:
DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]
Object permissions:
DENY
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ CASCADE ]
Arguments
ALL
Specifies that all applicable permissions are denied. For statement permissions, ALL can be used only by members of the sysadmin role. For object permissions, ALL can be used by members of the sysadmin and db_owner roles, and database object owners.
statement
Is the statement for which permission is denied. The statement list can include:
- CREATE DATABASE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
- BACKUP DATABASE
- BACKUP LOG
n
Is a placeholder indicating that the item can be repeated in a comma-separated list.
TO
Specifies the security account list.
security_account
Is the name of the security account in the current database affected by the denied permission. The security account can be a:
- Microsoft® SQL Server™ user.
- SQL Server role.
- Microsoft Windows NT® user.
- Windows NT group.
When a permission is denied from a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is denied from a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role, regardless of the permissions that have been granted to the members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence.
Two special security accounts can be used with DENY. Permissions denied from the public role are applied to all users in the database. Permissions denied from the guest user are used by all users who do not have a user account in the database.
When denying permissions to a Windows NT local or global group, specify the domain or computer name the group is defined on, followed by a backslash, then the group name. However, to deny permissions to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name.
PRIVILEGES
Is an optional keyword that can be included for SQL-92 compliance.
permission
Is a denied object permission. When permissions are denied on a table or a view, the permission list can include one or more of these statements: SELECT, INSERT, DELETE, or UPDATE.
Object permissions denied on a table can also include REFERENCES, and object permissions denied on a stored procedure or extended stored procedure can include EXECUTE. When permissions are denied on columns, the permissions list can include SELECT or UPDATE.
column
Is the name of the column in the current database for which permissions are denied.
table
Is the name of the table in the current database for which permissions are denied.
view
Is the name of the view in the current database for which permissions are denied.
stored_procedure
Is the name of the stored procedure in the current database for which permissions are denied.
extended_procedure
Is the name of an extended stored procedure for which permissions are denied.
user_defined_function
Is the name of the user-defined function for which permissions are being denied.
CASCADE
Specifies that permissions are denied from security_account as well as any other security accounts granted permissions by security_account. Use CASCADE when denying a grantable permission. If CASCADE is not specified and the specified user is granted WITH GRANT OPTION permission, an error is returned.
Remarks
If the DENY statement is used to prevent a user from gaining a permission and the user is later added to a group or role with the permission granted, the user does not gain access to the permission.
If a user activates an application role, the effect of DENY is null for any objects the user accesses using the application role. Although a user may be denied access to a specific object in the current database, if the application role has access to the object, the user also has access while the application role is activated.
Use the REVOKE statement to remove a denied permission from a user account. The security account does not gain access to the permission unless the permission has been granted to a group or role in which the user is a member. Use the GRANT statement to both remove a denied permission, and explicitly apply the permission to the security account.
Note DENY is a new keyword in SQL Server version 6.x compatibility mode. DENY is needed to specifically deny a permission from a user account, because in SQL Server version 7.0 REVOKE removes only previously granted or denied permissions. Existing SQL Server 6.x scripts that use REVOKE may have to be changed to use DENY to maintain behavior.
Permissions
DENY permissions default to members of the sysadmin, db_owner, or db_securityadmin roles, and database object owners.
Examples
A. Deny statement permissions
This example denies multiple statement permissions to multiple users. Users cannot use the CREATE DATABASE or CREATE TABLE statements unless they are explicitly granted the permission.
DENY CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]
B. Deny object permissions within the permission hierarchy
This example shows the preferred ordering of permissions. First, SELECT permissions are granted to the public role. After this, specific permissions are denied for users Mary, John, and Tom. These users then have no permissions to the authors table.
USE pubs
GO
GRANT SELECT
ON authors
TO public
GO
DENY SELECT, INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
C. Deny permissions to a SQL Server role
This example denies CREATE TABLE permissions to all members of the Accounting role. Even if existing users of Accounting have been explicitly granted CREATE TABLE permission, the DENY overrides that permission.
DENY CREATE TABLE TO Accounting