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.