SQL Server Column Level Permissions

Protect columns in SQL Server

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data?

Column Level Permissions

Column level permissions provide a more granular level of security for data in your database. You do not need to execute a separate GRANT or DENY statements for each column; just name them all in a query:

GRANT SELECT ON data1.table (column1, column2) TO user1;  

GO  

DENY SELECT ON data1.table (column3) TO user1;  GO

If you execute a DENY statement at table level to a column for a user, and after that you execute a GRANT statement on the same column, the DENY permission is removed and the user can have access to that column. Similarly, if you execute GRANT and then DENY, the DENY permission will be in force.

Steps to perform:

Create user on respective database

.

Go  to user properties >> click on search >> add object >> ok

select object type as table >> click ok >> 

click on browse >> choose matching objects(Table)

Select object then you can find permissions for object >> choose select

you will get highlight column permission.

Click on column permissions choose required columns

Same privileges we can grant by below code

use [NORTHWND]

GO

GRANT SELECT ON [dbo].[Employees] ([EmployeeID],[Country]),[City]) TO [sqldbahub]

GO

Hope article is user friendly.