REVOKE from user/role

Revoke permissions granted or denied to a database user, database role, or application role.

Syntax
      REVOKE [ GRANT OPTION FOR ] permission  [ ,...n ] 
         {TO | FROM} grantee_principal [,...n ]
            [CASCADE]
               [AS grantor_principal]

      REVOKE [GRANT OPTION FOR] permission [,...n]  
         ON 
           {  [ USER :: database_user ]
            | [ ROLE :: database_role ]
            | [ APPLICATION ROLE :: application_role ]
           }
         {FROM | TO} database_principal [ ,...n ]
            [CASCADE ]
               [AS database_principal]


      REVOKE [GRANT OPTION FOR] db_permission | ALL [ PRIVILEGES ] [,...n ]
         [ON LOGIN::SQL_Server_login ]  
            {TO | FROM } database_principal [ ,...n ] 
               [CASCADE ]
                  [AS database_principal ]
Key:

   database_principal
                user /role /application role
                user mapped to a Windows login/group/certificate
                user mapped to an asymmetric key
                user with no login

   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 revoke from other principals that have been
                granted or denied by this principal.

   permissions
                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

Not all of the permissions above can be granted to or revoked from a database principle (REVOKE...ON LOGIN::..)

When revoking the cascade of permission rights from one principle to another principle (REVOKE permission ON user/role FROM user/role) then the only valid permissions to revoke 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

If a permission was granted including the GRANT OPTION specified then it must be revoked with the CASCADE option.

Example

USE MyDb;
REVOKE VIEW DEFINITION ON ROLE::MyRole
    FROM MyUser CASCADE;
GO

REVOKE CREATE CERTIFICATE FROM MyUser;
GO

"Promote yourself, but do not demote another" - Israel Salanter

Related commands:

REVOKE Object permissions
GRANT Object permissions
GRANT User/Role permissions
DENY User/Role permissions
Equivalent Oracle command: REVOKE