SQL Server: Grant Permission To A Particular Table

Introduction

Sometimes it's necessary to restrict unusual access, especially when you have a number of users using a SQL server and you need to give them permissions on specific objects/tables.

Let’s get started with SSMS-2014

Let’s log in to Management Studio with the 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 a new SQL user. Provide a password if you'd like and then please un-check the option of “Enforce password expiration,” this will ask for a new password every time you set it to check.

Hit the 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 the Security > User section.

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

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

Click On the Browse button.

Choose a previously created user from this list. Click OK.

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

Let’s disconnect our default user “sa”, to log in with our new user, “Shekhar”.

Provide the user details again.

Here we can see the particular table that 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 into the table.

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

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