SQL Server Grant Permission to Particular Table

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’.

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

Right click on Logins > Choose New Login

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.

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

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

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

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

Click on Browse button.

Choose previous created user from this list. Click OK.

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.

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

Provide the user details again.

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

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

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

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.

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

This is it. Hope this will help! :)