GRANT and REVOKE statements in Transact SQL

Within SQL, DCL (Data Control Language) is the language in charge of managing access to the database and its contents, mainly through the GRANT statement (to grant permissions and access permissions to the users) and the REVOKE statement (to remove existing permissions).

GRANT

The GRANT statement allows you to grant permissions or permissions on a securable to a principal.

• Securable: A resource to which the SQL Server authorization system regulates access. For example, a database, a table, a stored procedure…

• Principal: Entity that can request the SQL Server resource. For example, a user.

• Permissions: Restrictions of a principal (user) on a securable (function, table, procedure…)

The basic syntax of the GRANT statement is as follows:

GRANT [permissions]
ON securable
TO principal;

To exemplify how the GRANT statement works, we are going to create a new user to grant permissions on:

CREATE USER Rebeca
FOR LOGIN Rebeca;

Once the new user is created: Rebecca, we will grant it the following permissions:

GRANT SELECT
ON Customers
TO Rebecca;

GRANT SELECT, INSERT, UPDATE, DELETE
ON Employees
TO Rebecca;

This way, we are granting read-only permissions to the user Rebecca (Execute SELECT) on the Customers table, while on the Employees table, we are granting read and write permissions to be able to consult records, insert new ones, modify them and/or delete them.

REVOKE

The REVOKE statement allows you to remove or revoke previously granted permissions on a securable for a principal.

REVOKE will often be used to remove and restrict permissions of certain operations to specific users

The basic syntax of the REVOKE statement is as follows:

REVOKE [permissions]
ON securable
FROM principal;

To exemplify how the REVOKE statement works, we are going to remove the permissions granted to the newly created user: Rebecca, on the tables: Customers and Employees

REVOKE SELECT
ON Customers
TO Rebecca;

REVOKE ALL
ON Employees
TO Rebecca;

It is also possible to remove permissions partially. For example, we could have only removed the data manipulation permissions on the Employees table;

REVOKE INSERT, UPDATE, DELETE
ON Employees
TO Rebecca;

In this way the user Rebecca could continue querying the table Employees.