Deny permissions to a database user, database role, or application role.
Syntax
DENY permission [ ,...n ]
TO grantee_principal [,...n ]
[CASCADE]
[AS grantor_principal]
DENY permission | ALL [ PRIVILEGES ] [,...n ]
[ON USER::user | ON ROLE::role | ON APPLICATION ROLE::role]
TO principal [ ,...n ] [CASCADE]
[AS principal ]
Key:
principal one of:
user /role /application role
user mapped to a Windows login/group/certificate
user mapped to an asymmetric key
user not mapped to a server principal.
grantee_principal/grantor_principal
SQL Server login or SQL Login mapped to a Windows login/group/certificate/asymmetric_key
The grantor_principal for an Endpoint must be a SQL Server login
cascade Also DENY from other principals that have been
granted or denied by this principal.
permission
ALL (= BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT,
CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW)
ALTER
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
CREATE AGGREGATE
CREATE ASSEMBLY
UNSAFE ASSEMBLY
EXTERNAL ACCESS ASSEMBLY
CREATE ASYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
AUTHENTICATE
AUTHENTICATE SERVER
BACKUP DATABASE
BACKUP LOG
ADMINISTER BULK OPERATIONS
ALTER ANY CONNECTION
ALTER ANY CREDENTIAL
CHECKPOINT
CONNECT
CONNECT REPLICATION
CONTROL
CREATE CERTIFICATE
ALTER ANY CERTIFICATE
CREATE CONTRACT
ALTER ANY CONTRACT
VIEW DATABASE STATE
CREATE DATABASE
CREATE ANY DATABASE
ALTER ANY DATABASE
VIEW ANY DATABASE
CREATE DDL EVENT NOTIFICATION
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE TRACE EVENT NOTIFICATION
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
CREATE DEFAULT
VIEW DEFINITION
VIEW ANY DEFINITION
DELETE
CREATE ENDPOINT
ALTER ANY ENDPOINT
ALTER ANY EVENT NOTIFICATION
EXECUTE
CREATE FULLTEXT CATALOG
ALTER ANY FULLTEXT CATALOG
CREATE FUNCTION
INSERT
ALTER ANY LINKED SERVER
ALTER ANY LOGIN
CREATE MESSAGE TYPE
ALTER ANY MESSAGE TYPE
TAKE OWNERSHIP
CREATE PROCEDURE
CREATE QUEUE
SUBSCRIBE QUERY NOTIFICATIONS
ALTER RESOURCES
REFERENCES
CREATE REMOTE SERVICE BINDING
ALTER ANY REMOTE SERVICE BINDING
CREATE ROLE
ALTER ANY ROLE
CREATE ROUTE
ALTER ANY ROUTE
CREATE RULE
CREATE SCHEMA
ALTER ANY SCHEMA
SELECT
ALTER SERVER STATE
VIEW SERVER STATE
ALTER SETTINGS
CREATE SERVICE
ALTER ANY SERVICE
SHOWPLAN
SHUTDOWN
CONNECT SQL
CREATE SYMMETRIC KEY
ALTER ANY SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
ALTER TRACE
ALTER ANY DATABASE DDL TRIGGER
CREATE TYPE
ALTER ANY USER
UPDATE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
When denying the cascade of permission rights from one principle to another principle (DENY permission ON user/role TO user/role) then the only valid permissions to deny are:
CONTROL, IMPERSONATE, TAKE OWNERSHIP, ALTER, VIEW DEFINITION
For user: CONTROL/IMPERSONATE/ALTER/VIEW DEFINITION
For role: CONTROL/TAKE OWNERSHIP/ALTER/VIEW DEFINITION
For app. role: CONTROL/ALTER/VIEW DEFINITION
Examples
USE MyDb;
DENY CONTROL ON USER::User78 TO JohnDoe; GO DENY VIEW DEFINITION ON ROLE::SupervisorRole
TO User64 WITH GRANT OPTION; GO DENY IMPERSONATE ON USER::User78 TO SupervisorRole;
GO
"I had one guy at a gas station in New York say to me, 'Hey, you look like Hugh Grant. No offense'" ~ Hugh Grant, on being recognized in public
Related commands:
REVOKE User/Role permissions
GRANT Object permissions
sys.database_permissions
sys.database_principals
Equivalent Oracle command: GRANT