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](/KB/database/1103206/sql_1.png)
Here, we will create a new user to perform SQL operations, let’s create a new user to set the access permission.
![Image 2](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Right click on Logins > Choose New Login
![Image 3](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
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](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Hit OK button. Now map the user to a particular database. In my case, I am using a “sample” database.
![Image 5](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
As you can see, our new user is listed below in Security > User section.
![Image 6](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Now let’s set permission to that user to a particular operation on this table. Right click on Table > Choose Properties.
![Image 7](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
A table property window will appear Choose Permission from left tab, then click Search button to find user/role.
![Image 8](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Click on Browse button.
![Image 9](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Choose previous created user from this list. Click OK.
![Image 10](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
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](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Let’s disconnect our default user “sa
”, to login with our new user “shekhar
”.
![Image 12](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Provide the user details again.
![Image 13](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Here, we can see the particular table which the user has permitted.
![Image 14](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Now let’s run a select
query, you can see there’s no problem at all to select the table data.
![Image 15](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
Let’s try to insert a row, you can see it’s inserted the row to the table.
![Image 16](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
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](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
We won’t able to perform a delete
operation on this table until the user has delete
permission.
![Image 18](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
This is it. Hope this will help! :)