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 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
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
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.
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)