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.
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}
}
TO security_account[,...n]
[CASCADE]
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.
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.
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.
DENY permissions default to members of the sysadmin, db_owner, or db_securityadmin roles, and database object owners.
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]
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
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
GRANT | REVOKE |
sp_helprotect | Backward Compatibility |
Preventing Access by Denying Permissions |