How to undo a DENY

Introduction

Yesterday I showed you that a DENY is durable, meaning a DENY gets persisted by SQL Server the same way a GRANT does. If you have used the DENY statement before, or maybe while reading yesterday's post, you might have had the question what you can do if you change your mind on a DENY and what to undo it now.

Undoing a DENY

One way to deal with this is to just GRANT the same permission, as successive GRANT and DENY statements overwrite each other. However that might have other side effects down the road that you do not want, especially if you are dealing with roles.

You could go and REVOKE the GRANT after you used it to overwrite the DENY. However, there is a much simpler way. I said yesterday that a DENY is stored in the same permissions list that a GRANT would be stored in. The only difference between the two is actually just a "state" value on the entry, that is either "G" for GRANT or "D" for DENY. That means that you can REVOKE a DENY the same way you REVOKE a GRANT.

So far you might have had the impression that a REVOKE always reduces permissions. But that is not actually accurate. Instead, the REVOKE statement removes an entry from SQL Server's internal permissions list, no matter if that entry was a GRANT or a DENY. So after executing a REVOKE the target principal might just have more permissions than before.

REVOKE a DENY

Let's look at an example. First I am going to create our schema, table and user that we have become accustomed to over the last few posts:

Now we can use a DENY SELECT on the schema to override a GRANT SELECT on the table:

So far this is not surprising. Now let's see what happens if we REVOKE SELECT on the schema from our user:

Because the REVOKE removed the existing DENY, TestUser1 can now access the table.

- Let that sink in for a second. -

The REVOKE actually increased the active permissions for TestUser1 as it removed the preexisting DENY. Also, there is no way to tell just by looking at the REVOKE statement itself. The syntax to remove a granted permission is exactly the same as the syntax to remove a denied permission.

When revoking permissions you need to therefore always pay attention to actually revoking the intended permission. Just executing a REVOKE might otherwise have the totally opposite effect of what you were looking for.

Summary

The REVOKE statement can be used to remove not only prior grants but also prior denies. That means that executing a REVOKE statement can effectively increase the active permissions.