REVOKE

Revoke permissions on system objects, database objects,

Syntax
      REVOKE SELECT ON [sys.]system_object FROM principal

      REVOKE EXECUTE ON [sys.]system_object FROM principal

      REVOKE [GRANT OPTION FOR] object_permission [,...n ] 
         ON Object
            {TO | FROM} principal [,...n ]
               [CASCADE]
                  [AS principal ]

Key:
   Objects
          ASSEMBLY :: assembly
          ASYMMETRIC KEY :: asymmetric_key
          CERTIFICATE :: certificate
          CONTRACT :: contract
          ENDPOINT :: endpoint
          FULLTEXT CATALOG :: full-text_catalog
          LOGIN :: SQL_Server_login
          MESSAGE TYPE :: message_type
          [OBJECT ::][schema].object [(column [,...n ])]
          REMOTE SERVICE BINDING :: remote_binding
          ROUTE :: route
          SCHEMA :: schema 
          SERVICE :: service
          SYMMETRIC KEY :: symmetric_key 
          TYPE :: [schema].type 
          XML SCHEMA COLLECTION :: [schema.] XML_schema_collection

   system_object  Objects such as stored procedures, functions and views.

   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.

   cascade      Also revoke from other principals that have been
                granted or denied by this principal.
                Cascade WITH GRANT will revoke both GRANT and DENY of the permission

   object_permission
                ALTER/CONTROL/RECEIVE/REFERENCES/IMPERSONATE/
                SELECT/UPDATE/INSERT/DELETE/
                EXECUTE/TAKE OWNERSHIP/VIEW DEFINITION
                ALL - Against an object, REVOKE ALL will revoke all permissions applicable to the object.

When revoking from a SQL_Server_login the AS clause (if present) should specify the SQL Server login from which the principal executing this query derives its right to revoke the permission.

Example

USE MyDb;
REVOKE EXECUTE ON sys.MyStoredproc FROM public;
GO

REVOKE SELECT ON OBJECT::MyTable.MyColumn FROM MyUser;
GO

"I don't know if I believe in role models. We're all so different; we're all individuals. In the long run, that's what matters" - Wilson Cruz

Related commands:

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


© Copyright SS64.com 1999-2014
Some rights reserved