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 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 a new password every time if you set it  to 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 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 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 allows the user access to perform operations 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 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.