Permissions: Database Object Permissions

Permissions: Database Object Permissions

We have various database objects such as view, stored procedures, triggers, functions and indexes in a relational database.

SQL Server: Grant/Revoke Privileges

Grant and revoke privileges in SQL Server (Transact-SQL) with syntax and examples.

You can GRANT and REVOKE privileges on various database objects in SQL Server. We'll look at how to grant and revoke privileges on tables in SQL Server.

Grant Privileges on Table

You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Permission for object level:

Permission for database level:

Syntax

The syntax for granting privileges on a table in SQL Server is:

GRANT privileges ON object TO user;

Object: 

The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.

User: 

The name of the user that will be granted these privileges.

Privileges:

The privileges to assign. It can be any of the following values:

Examples:

--Assign database read permission to user

USE [TESTDB]
ALTER ROLE [db_datareader] ADD MEMBER [ROCK]

--Assigning the SELECT and INSERT the table level permission

GRANT SELECT,INSERT ON TEST TO ROCK; 

Revoke Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

Syntax

The syntax for revoking privileges on a table in SQL Server is:

REVOKE privileges ON object FROM user;

object:

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user:

The name of the user that will have these privileges revoked.

privileges:

It is the privileges to assign. It can be any of the following values:

Example

Let's look at some examples of how to revoke privileges on tables in SQL Server.
For example, if you wanted to revoke DELETE privileges on a table called employees from a user named anurag, you would run the following REVOKE statement:

REVOKE DELETE ON employees FROM anurag;

If you wanted to revoke ALL permissions (ie: SELECT, INSERT, UPDATE, DELETE, and REFERENCES) on a table for a user named anurag, you could use the ALL keyword as follows:

REVOKE ALL ON employees FROM anurag;