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