Click here to Skip to main content
14,921,505 members
Articles / Programming Languages / T-SQL
Tip/Trick
Posted 27 May 2016

Tagged as

Stats

16.9K views
4 bookmarked

SQL Server Grant Permission to Particular Table

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
27 May 2016CPOL2 min read
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)

Share

About the Author

Shashangka Shekhar
Software Developer (Senior) s3 Innovate Pte Ltd
Bangladesh Bangladesh
Hi, I am Shashangka Shekhar,

Working with Microsoft Technologies. Since March 2011, it was my first step to working with Microsoft Technologies, achieved bachelor’s degree on Computer Science from State University of Bangladesh(Dhaka). Have 8+ years of professional experience, currently working as Senior Software Engineer at s3 Innovate Pte Ltd.

I believe in desire of learning & also love to be a part of .Net Community by sharing knowledge’s.

Comments and Discussions

 
QuestionGRANT Pin
Smaily Marroquin14-Jun-16 11:52
MemberSmaily Marroquin14-Jun-16 11:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.