GRANT and REVOKE Permission in SQL Server

In my previous article we learned the types of commands categorized in SQL Server. We've also learned about GRANT and REVOKE permissions in SQL Server.

This article shows how to grant permission on specific objects to a user in database. For this example, we've created a login with one mapped database without any server or database role. The following is the requirement:

Server: LOCAL
Database: CSharpCorner
Login: CSharpCorner
Server Role: NONE.
Database mapped: CSharpCorner
Our requirement: To create a table in database and then to perform select, insert, delete and update operations on the new table by a New User.

To perform this activity we require 2 sessions, 1 session for Admin and another session for our new user.

2 Sessions because, from the admin session we'll give right/permission. And the other session for the user.

So let's look at an example.

Let's create the first table named "tblArticles", the following is the command for it.

CREATE TABLE tblArticles
(
Id int primary key identity,
ArticleName varchar(10),
AuthorName char(10)
)

If we execute this query, we'll get an error message as shown below.

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'CSharpCornerDB'.

This is because the user "CSharpCorner" doesn't have any permission to create a table.

If you want to check the permission on the table, use the following procedure.

From the Admin session, Expand your database then Expand Security then Expand Users then select User then right-click on the User and select Properties.

After this your Database User window will open, select Securables then click on Search as shown below.

Now we'll add the type of object to give the permission to. When you click on Search it'll ask you to select the type of object.

add object

Select "All objects of the types..." then click OK.

Now it'll ask you to select an object. You can select any object on which you want to give permission. We'll select Database since we're dealing with the creation of a table in a database. Click OK after adding your objects.

Once you have added your objects, it'll be displayed in the Database User window as shown below.

user name

You'll also see the types of permissions on this object in the same window as shown below.

explict

Now, since we want to give Create table permission to the user on the database, we'll search for that item in the list. Once found, select the GRANT checkbox and click OK.

The following is the query for the preceding procedure.

use [CSharpCornerDB]
GO
GRANT CREATE TABLE TO [CSharpCorner]
GO

Now let's create a table with a new User, in other words CSharpCorner.

USE CSharpCornerDB
GO
CREATE table tblArticles
(
ArticleId int primary key identity,
ArticleName varchar(10),
Category varchar(10)
)

Execute the preceding query and Ohhh! We encountered an error?

Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Surprise ahh!!! This is because, now we've permission to create table but we don't have permission on the schema yet, on the default schema, in other words dbo.

Not to worry, just use the preceding procedure as you followed for tables and database and add the schema to your list.

dob

T-SQL to Grant ALTER permission.

use [CSharpCornerDB]
GO
GRANT ALTER ON SCHEMA::[dbo] TO [CSharpCorner]
GO

After giving alter permission to the user, your user can create a table as shown below.

database

Now, let's fire a select query to see any records in the table.

select * from tblArticles

When you execute this select statement, again you'll get an error as shown below.

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tblBarCOdes', database 'CSharpCornerDB', schema 'dbo'.

This means that you don't even have select permission on that table.

To grant select permission, use the following procedure.

Expand your database then Expand Security then Expand Users then select User then right-click on User and Select Properties -> On Database User window Select -> Securables then click on Search then select All Object of type -> Check Tables checkbox from object type windows then click OK.

After using the preceding procedure, you'll get something as in the following.

table design

Now grant the user SELECT Permission and click on OK.

The code for the preceding step is shown below.

use [CSharpCornerDB]
GO
GRANT SELECT ON [dbo].[tblArticles] TO [CSharpCorner]
GO

We've given SELECT permission to the user "CSharpCorner". Now our SELECT query works perfectly.

table

Currently the user has only SELECT and ALTER permission, but he cannot enter any records into the table.

To give INSERT permission, run the following query.

use [CSharpCornerDB]
GO
GRANT INSERT ON [dbo].[tblArticles] TO [CSharpCorner]
GO

With this permission, the user also has access to enter records into the table.

Now add 3 records.

use [CSharpCornerDB]
GO
INSERT INTO tblArticles VALUES ('ABCD','Abhishek'),('XYZ','Mahesh'),('MNOP','Sumit')
GO

Now let's delete the third record from the table.

use [CSharpCornerDB]
GO
DELETE FROM tblArticles WHERE Id = 3
GO

But the user doesn't have DELETE permission as well and he'll not be able to delete records and will get an error as in the following:

Msg 229, Level 14, State 5, Line 1
The DELETE permission was denied on the object 'tblArticles', database 'CSharpCornerDB', schema 'dbo'.

To give DELETE permission:

use [CSharpCornerDB]
GO
GRANT DELETE ON [dbo].[tblArticles] TO [CSharpCorner]
GO

Now delete the third record and you'll be left with 2 records.

author name

Now we want to take back the delete permission from the user. To do that you can either go through the GUI or T-SQL.

The simplest way is using the GUI. Just go to Securables then select tblArticles then untick Delete permission and you're done.

If you want to do it using T-SQL, the following is the query.

use [CSharpCornerDB]
GO
REVOKE DELETE ON [dbo].[tblArticles] TO [CSharpCorner]
GO

Now, you don't have any DELETE permission for the table.

You can also remove all the preceding permissions at once as shown below.

use [CSharpCornerDB]
GO
REVOKE SELECT, DELETE, INSERT ON [dbo].[tblArticles] TO [CSharpCorner]
GO

To give all these 3 permissions on the table at once, run the following query.

use [CSharpCornerDB]
GO
GRANT SELECT, DELETE, INSERT ON [dbo].[tblArticles] TO [CSharpCorner]
GO

Now we're done with the CREATE, DELETE, INSERT and SELECT permissions on tables.

But do you know what, you can also give permission on columns!

If you want the user to give SELECT permission on columns, just use the following.

On clicking the Column properties you'll get the following column Permission window to grant permission on columns.

Select your columns on which you want to grant permission and click OK.

select

Now you can see that instead of a Tick mark, the checkbox is filled with a color, which means that the column permission is activated on a table.

Now when you try to run your following select query, you'll get an error.

select * from tblArticles

Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column 'Id' of the object 'tblArticles", database 'CSharpCornerDB', schema 'dbo'.

If you see the error, it says that permission is denied on the ID column, because the query is using "*" which means extract all columns. Hence instead of "*" you need to use column names as shown in the following query.

SELECT ArticleName, AuthorName FROM tblArticles

This query will execute successfully and gives you the correct output as shown below.

article name

To again get SELECT permission on all columns run the following query.

USE CSharpCornerDB
GO
GRANT SELECT ON [dbo].[tblArticles] TO [CSharpCorner]
GO

Output:

Output

So, this was the article on GRANT and REVOKE permission in SQL Server. In this article we've learned how to give permission on any object. We also learned both ways to grant permission. We saw how to take back any granted permission.

In my future article we'll also learn how to give READ and WRITE permission on an object. Until then keep learning and keep sharing.

If there's any mistake in this article then please let me know. Please provide your valuable feedback and comments that enable me to provide a better article the next time.

7 Comments

Join the conversation! Your thoughts help the community grow.

Jaipal Reddy11y ago
good work
Nilesh Jadav11y ago
Nice one
Rakesh11y ago
Good One
Pankaj Kumar Choudhary11y ago
Good Article Sir...........
Santhakumar Munuswamy11y ago
Good Article! Thanks for sharing
Neeraj Kumar11y ago
Good one.
Gopi Chand11y ago
Great work :)