REVOKE: How to Undo Granted Permissions

Introduction

Over the last days we have been learning about the GRANT statement. Today we are going to investigate, what we can do to remove a privilege that had been granted before.

The REVOKE statement does exactly what we are looking for: It removes an existing permission. Let's look at an example.

REVOKE Example

First we need a table to grant a permission on and a user to grant the permission to:

With those two in place, we can grant a simple SELECT privilege on dbo.tst to TestUser1.

If you need a refresher on the GRANT statement, check out How to GRANT Permissions in T-SQL.

With that permission in place, TestUser can now SELECT from dbo.tst:

Now, if we decide that TestUser1 should not have SELECT access to our table anymore, we can use the REVOKE statement. The syntax of the REVOKE statement is very similar to the one of the GRANT statement:

The only real difference to the GRANT statement is the keyword FROM instead of TO in front of the grantee. However, even though it does not follow proper English grammar, SQL Server does allow TO to be used instead of FROM like this:

That syntax from, while it does look a little odd, makes automatically generating scripts a little easier.

The REVOKE statement causes a previously granted identical privilege to be revoked. It works only on the same privilege on the same securable for the same grantee. Revoking for example an UPDATE privilege does not have any effect on an existing SELECT or INSERT permission.

After executing the above REVOKE statement, TestUser1 does not have access to dbo.tst1 anymore:

Summary

The REVOKE statement can be used to undo the action of a GRANT statement. It revokes a privilege from a grantee, but only if that exact privilege was granted to the same grantee before.