This isn’t something you have to do frequently, but sometimes you don’t want the users to have access to certain columns in a table. For example let’s say you have a salary column in your employee table that you don’t want everyone seeing.

So why is this a problem? Well here is a simple example. I log in as Doctor and I run the following query:

SELECT * FROM Employee

And I get this error:

Msg 230, Level 14, State 1, Line 13
The SELECT permission was denied on the column ‘Salary’ of the object ‘Employee’, database ‘Test’, schema ‘dbo’.

We can avoid the error if we just query the specific columns (which is what we should be doing anyway) but a lot of code is libel to break. I also want to point out that not only did this cause an error but it also let the user know that a Salary column even existed.

Which leaves us with:

Access through views

Otherwise known as the easy way.

We create a view that doesn’t include the column(s) we don’t want them to see.

Now if Doctor tries to query against the Employee table they get a standard The SELECT permission was denied on the object ‘Employee’ error with no mention of any of the columns. But if they query the view EmployeeList they get the data we want them to have. And as a bonus both SELECT * or SELECT columnlist will work.