SHASHANGKA SHEKHAR'S BLOG

Hi, I am Shashangka Shekhar, Working with Microsoft Technologies. This is my personal blog where I share some of my experiences.

SQL Server User Access Permission to Particular Table in Database

In this post we will create a new user in SQL Server Management Studio (SSMS) & allow access to that user to perform specific operation.

Let’s get started with SSMS-2014

sql_1

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

sql_2

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

Right click on Logins > Choose New Login

sql_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 u like to & then please un-check the option of “Enforce password expiration”, this will ask new password every time if you set it checked.

sql_4

hit OK button. Now map the user to a particular database. In my case i am using a “sample” database.sql_5

as you can see our new user is listed below in Security > User section

sql_6

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

sql_7

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

sql_8

Click Browse button

sql_9

Choose previous created user from this list. Click OK

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

sql_11

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

sql_12

Provide the user details again.

sql_13

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

sql_14

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

sql_15

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

sql_16

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

sql_17

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

sql_18

 

Hope this will help 🙂

Post a comment