Have you ever been confused about the differences between the GRANT, DENY, and REVOKE commands in SQL Server? You’re not alone. These commands can be easily misunderstood, but once you grasp their concepts, they become powerful tools for managing permissions in your database.

Let’s start by clarifying the basics. GRANT and DENY are opposites. GRANT applies a positive permission, while DENY applies a negative permission. In other words, GRANT allows a user to perform a specific action, while DENY restricts that action.

For example, if we want to allow a user named “MyUser” to run a SELECT statement against any table, view, or table-valued function in the database, we can use the GRANT command:

GRANT SELECT TO [MyUser];

On the other hand, if we want to deny the same user from running a SELECT statement, we can use the DENY command:

DENY SELECT TO [MyUser];

At first glance, DENY might not seem like a permission, but if we examine the system views where the permission data resides, we can see that both GRANT and DENY commands add a permission entry:

SELECT Perms.*
FROM sys.database_permissions Perms
JOIN sys.database_principals Users
ON Perms.grantee_principal_id = Users.principal_id
WHERE Users.name = 'MyUser';

If we run the GRANT command and then re-run the query, we will see a GRANT entry instead of the DENY. This confirms that both commands are indeed adding permissions.

Now, what if we want to remove a previously granted or denied permission? This is where the REVOKE command comes into play:

REVOKE SELECT TO [MyUser];

Running the same query again, we can see that the DENY entry is no longer present. It’s important to note that you don’t have to specify that you are revoking the DENY. You simply revoke the permission itself.

One important thing to remember is that DENY always overrides GRANT. Even if a user is granted a permission at multiple levels (object level, database level, AD groups, and database roles), a single DENY will override all of them.

So, in summary, GRANT and DENY are opposites, and together they are the opposite of REVOKE. GRANT allows a positive permission, DENY applies a negative permission, and REVOKE removes a permission. DENY always takes precedence over GRANT.

Understanding these concepts will help you effectively manage permissions in your SQL Server database and ensure the security of your data.

Thank you for reading!