DENY to user/role

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