DENY Object permissions

Deny permissions to system objects, database objects,

Syntax
      DENY SELECT ON [sys.]system_object FROM principal

      DENY EXECUTE ON [sys.]system_object FROM principal

      DENY object_permission [,...n ] 
        ON Object
           TO 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 DENY from other principals that have been
                granted or denied by this principal.

   object_permission
                ALTER/CONNECT/CONTROL/RECEIVE/REFERENCES/IMPERSONATE
                SELECT/UPDATE/INSERT/DELETE/
                EXECUTE/TAKE OWNERSHIP/VIEW DEFINITION
                ALL Against an object, DENY ALL will deny all permissions applicable to the object.
                (Not every object_permission is applicable to every type of Object)

When denying rights 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 deny the permission.

Example

USE MyDb;
DENY EXECUTE ON sys.MyStoredproc TO public;
GO DENY SELECT ON MyTable.MyColumn TO 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:

REVOKE Object permissions
GRANT Object permissions
Equivalent Oracle command: REVOKE


© Copyright SS64.com 1999-2014
Some rights reserved