SQL Server uses a permissions model based on discrete permissions and permission inheritance. It adopts a simple and fine-grained approach to provide control over secured entities (objects or statements). The permissions can be granted, denied, or revoked from a user or role. These aspects are the fundamental building blocks of SQL Server security management. Let us understand Grant, Deny, and Revoke Permissions.
Grant
The GRANT
 command in T-SQL is used to give users permission to execute SQL Server operations like SELECT, INSERT, UPDATE, DELETE on SQL Server objects like tables, views, and stored procedures.
For instance, if you have a database for a library system and you want to give a user named Librarian
 access to select data from a table named Books
, you would use the following T-SQL command:
GRANT SELECT ON Books TO Librarian;
This statement gives the Librarian
 user permission to execute SELECT queries on the Books
 table.
Deny
DENY
is used to prevent a security principal (user or role) from performing an operation. Even if a user has been granted permission, the user cannot execute that operation if the same permission is explicitly denied. Deny always takes precedence.
For example, if we want to prevent the Librarian
 from deleting records from the Books
 table, we would use the following statement:
DENY DELETE ON Books TO Librarian;
Even if the Librarian
 has been granted full access on Books
 table through a role, this DENY
 statement will prevent the deletion of records from Books
.
Revoke Permissions
The REVOKE
the command removes a previously granted or denied permission. It does not prevent a user from executing an operation if that operation is permissible due to permissions granted through other roles.
For example, if we want to remove the SELECT
 permission on the Books
 table from the Librarian
 user, we would use the following T-SQL command:
REVOKE SELECT ON Books FROM Librarian;
It’s important to note that REVOKE
only removes the permission granted or denied directly to a user or role. It does not affect permissions granted to other roles the user is a member of.
Conclusion
GRANT
, DENY
, and REVOKE
 are essential commands for managing permissions in SQL Server. They provide fine-grained control over who can do what with various secured entities, allowing administrators to maintain security while offering users the access they need to perform their tasks.
These commands are part of the larger topic of managing security in SQL Server, including aspects such as authentication, roles, and schemas.
You can watch my YouTube videos over here.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)