Difference between GRANT, DENY and REVOKE
Grant is used to specifically allow access to an object.
Deny is used to specifically prevent access to an object.
Revoke is used to remove specifically granted or denied access to an object. [The Handbook for Reluctant Database Administrators by Josef Finsel]
Difference between GRANT, DENY and REVOKE can also be stated in below words
When permission is granted, the user or role is given permission to perform an acton, such as creating a table.
The DENY statement denies permission on an object and prevents the principal from gaining GRANT permission based on membership in a group or role.
The REVOKE statement removes a permission that was previously granted or denied.
Permissions denied at a higher scope in a security model (server at highest level, followed by database and schema) override grants on that permission at a lower scope. If INSERT permission is denied on tables at the database level, and INSERT on a specific table in that database is granted at the schema level, the result is that INSERT is denied on all tables. In this example a database-level DENY overrides any GRANT at the lower schema level. For more on this please refer [Microsoft SQL Server 2014 Unleashed by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein].
Simply difference between REVOKE and DENY can also be stated as
REVOKE removes a previously assigned permission. DENY prevents the permission from being assigned. [Mastering SQL Server 2000 Security by Mike Young, Curtis W. Young]
Examples of GRANT, DENY and REVOKE
1.
Granting SELECT on employee table to user1 and user2.
USE DB1;
GRANT SELECT ON employee
TO user1, user2;
2.
Granting CREATE TABLE to user1 and user2.
USE DB1;
GRANT CREATE TABLE
TO user1, user2;
3.
Denying CREATE TABLE to user1 and user2.
USE DB1;
DENY CREATE TABLE
TO user1;
4.
Revoking SELECT on table_1 from user2.
USE DB1;
REVOKE SELECT ON table_1
FROM user2;