SQL Server REVOKE

Summary: in this tutorial, you’ll learn how to use the SQL Server REVOKE statement to remove the previously granted permissions from a principal.

Introduction to the SQL Server REVOKE statement

The REVOKE statement removes previously granted permissions on a securable from a principal. The following shows the syntax of the REVOKE statement:

REVOKE permissions
ON securable
FROM principal;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify one or more permissions in the REVOKE clause.
• Second, specify a securable in the ON clause.
• Third, specify a principle in the FROM clause.

SQL Server REVOKE statement example

To follow the example, you need to complete the GRANT statement example that creates the user peter and grant the SELECT, INSERT, and DELETE permissions on the People table to the user peter.

First, connect the SQL Server using the system administrator (sa) account and use the REVOKE statement to remove the DELETE permission on the People table from the user peter:

REVOKE DELETE
ON People
FROM peter;Code language: SQL (Structured Query Language) (sql)

Second, connect to the SQL Server using the user peter and issue the DELETE statement to verify the permission:

DELETE FROM People;Code language: SQL (Structured Query Language) (sql)

Error:

The DELETE permission was denied on the object 'People', database 'HR', schema 'dbo'.Code language: SQL (Structured Query Language) (sql)

It works as expected.

Third, select data from the People table:

SELECT * FROM People;Code language: SQL (Structured Query Language) (sql)

Fourth, remove the SELECT and UPDATE permissions on the People table from the user peter:

REVOKE SELECT, INSERT
ON People
FROM peter;Code language: SQL (Structured Query Language) (sql)

Finally, switch the connection to the user peter and select data from the People table:

SELECT * FROM People;Code language: SQL (Structured Query Language) (sql)

Error:

The SELECT permission was denied on the object 'People', database 'HR', schema 'dbo'.Code language: plaintext (plaintext)

The error indicates that the revoke was executed successfully.

Summary

• Use SQL Server REVOKE statement to remove the previously granted permissions on a securable from a principal.