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)
