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

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

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

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.

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 operation on this table. Right click on Table > Choose Properties.

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

Click 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 the user can have access to perform operation 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 have no access of perform update operation on this table.

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

 

Hope this will help 🙂