User Restriction in SQL Server

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

UR1

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

UR2

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.

UR3

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.

UR4

4. Goto Securables tab.
5. Click on Search
6. Select “All Object of the Types”

UR5

7. Select “Tables”.

UR6

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”]

UR7

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.

UR8

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”.

UR9

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.

UR10

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s