User Restriction in SQL Server
Let’s see how we can restrict users to access only specific objects in Sql Server.
Let’s Create a Login
1. Connect to the SQL Server as SA user.
2. Goto Security–>Login–>New Login
3. In the “General” Tab, Enter the Login Name. Select SQL Server Authentication
4. Enter Password and Confirm Password.
5. Uncheck “Enforce Password Policy”
6. Click OK
7. Now we have Created a Login.
Next step is now we are going to Map this Login to a database user.
Goto the Database where you want to give access to the created Login.\
1. Expand the Database. Select Security–>New–>User.
2. Enter the Username. It is not necessary to have same as that of login name. But just to avoid confusion, I am
creating the username as same as Login name.
3. In the Login Name, Click on the browse button, and select the login that you have created just now.
4. Goto Securables tab.
5. Click on Search
6. Select “All Object of the Types”
7. Select “Tables”.
8. Click OK.
9. Select the table that you want to give the access.
10. In the below tab, Tick the “GRANT” option for “SELECT”.
This means, you are providing the user to view the records in the particular table.
[In the same window, you can select whichever tables that you want to provide access and also specify access levels like “select”, “Insert”, “update” and “delete”]
11. If you want to restrict the user even to the column level, Click on “Column Properties” and Select the Columns that you want to
give the access and give OK.
12. Click OK on the database user window.
Now we have done with the creating database user.
In short, we have created a user who have only access to one column named “C1” in the table “dbo.a” in the database “AdventureWorks2008R2”.
Now lets check this user access that we have created now.
Disconnect the SQL Server connection in the object explorer. Connect to the Sql Server using the login we have created just now.
Try to access some other database that the user doesn’t have access to.
It should throw error message as “The Database is not accessible”.
Expand the database “AdventureWorks2008R2” in object explorer. Expand “Tables”.
As you see, the only table dbo.a is listed out. because the user have access only to that particular table.
Run the Query “Select * from dbo.a”
You will get error message as
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c2’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c3’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.
If you run “Select c1 from dbo.A” then it will work. because we did a column level restriction for this user.
Also If you try to insert, update, or delete, then also you should get a permission denied error because we have not given permission for the user for those statements.
Like this:
Like Loading...
Keep Software Weird 