Restrict User by using Column level filtering in SQL Server

How to restrict the user on the column level  for the table in SQL server?

In this scenario we are going to restrict the user on the column level, so that user cannot able to view the data on the column or the user given access only to particular columns.

I have created a database named EMPLOYEE which has a table named EMP_INFO

 

Also I had created the sql login named USER1 who has access to the database EMPLOYEE

User1 has only access to connect to database EMPLOYEE and can run select query on the table EMP_INFO without restricting any coumn on the table.

Use the below query to view all the data on the table EMP_INFO:-

Use employee

go

execute as user = 'user1';

go

select * from [dbo].[emp_info]

go

Execute as user is the statement used to switch over the user, SQL server thinks that the user is logged on to execute the statement and it dynamically takes all the user permission of USER1.

Note:- Execute as user applies from SQL server 2008 to current version 2014.

 

User1 can view all the rows and columns on the table, but being a user its not advisable to view all the data on the table. In this scenario salary and phone number should not be viewed by other user’s  bec it’s a confidential data maintain within the company.

So we will restrict the column of salary and phone to the user1:-

• GOTO TABLE EMP_INFO and right click then click properties

• Click on the permission tab and click on search button on the right side

• Select the object types and click on Browse button which will provide the list of user’s whereas it tagged under the object types and then select the appropriate user.

•   Select the USER1 and Click Ok.

 

• Goto Permission and check SELECT permission and use checkbox GRANT which enables the column permission

 

• Click Column permission and use checkbox to give GRANT permission on the columns which you like to have access to the user1.

•   I had given grant access to EMPID & NAME columns for the user1 and DENY to columns phone and salary, then click Ok button.

 

Execute the same below query again:-

 Use employee

go
execute as user = 'user1';
go
select * from [dbo].[emp_info]
go

 

• We will get the above error message stating that select permission is denied on the column salary and phone.

• This confirms that the columns  was restricted for the user1

Execute the below query now:-

Use employee

go
execute as user = 'user1';
go
select name,empid from [dbo].[emp_info]
go

User1 was restricted to salary and phone columns alone, user can run the select query on the column name and empid were he has access to it.