Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / T-SQL

SQL Server Grant Permission to Particular Table

5.00/5 (3 votes)
28 May 2016CPOL2 min read 38.3K  
In this post, we will create a new user in SQL Server Management Studio (SSMS) & allow access to that user to perform a specific operation.

Introduction

Sometimes, it's very much needed to restrict unusual access, while you have a number of users using SQL server & give them permissions on a specific object/table.

Let’s Get Started with SSMS-2014

Let’s login to Management Studio with default user ‘sa’.

Image 1

Here, we will create a new user to perform SQL operations, let’s create a new user to set the access permission.

Image 2

Right click on Logins > Choose New Login

Image 3

In this window, we are going to name our new user, in my case, I am using my name as new SQL user. Provide a password if you like to & then please un-check the option of “Enforce password expiration”. This will ask for a new password every time if you set it checked.

Image 4

Hit OK button. Now map the user to a particular database. In my case, I am using a “sample” database.

Image 5

As you can see, our new user is listed below in Security > User section.

Image 6

Now let’s set permission to that user to a particular operation on this table. Right click on Table > Choose Properties.

Image 7

A table property window will appear Choose Permission from left tab, then click Search button to find user/role.

Image 8

Click on Browse button.

Image 9

Choose previous created user from this list. Click OK.

Image 10

Here we go, choose the grant option from the below portion for our new user which the user can have access to perform operation on our selected table. Click Ok.

Image 11

Let’s disconnect our default user “sa”, to login with our new user “shekhar”.

Image 12

Provide the user details again.

Image 13

Here, we can see the particular table which the user has permitted.

Image 14

Now let’s run a select query, you can see there’s no problem at all to select the table data.

Image 15

Let’s try to insert a row, you can see it’s inserted the row to the table.

Image 16

This time, the query executed with an error of permission issue, as we know this user will have no access of perform update operation on this table.

Image 17

We won’t able to perform a delete operation on this table until the user has delete permission.

Image 18

This is it. Hope this will help! :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)