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.

There are two fairly simple options.

CREATE TABLE Employee (
EmployeeId int NOT NULL IDENTITY (1,1),
Name varchar(255),
Address1 varchar(255),
Address2 varchar(255),
City varchar(255),
State varchar(255),
Zip varchar(50),
Salary money
);

Column level security

Otherwise known as the hard way. Here you grant permissions to just the columns of the table that you want someone to have access to.

Open the permissions tab for the user and add the object you are interested in. Check Grant for the SELECT permission. Then hit the COLUMN PERMISSIONS button.

Select the column level permissions we are interested in. Note we did not check Grant for the Salary column.

Now instead of a check in the box there’s a square. This tells us that the permissions are not uniform across all of the columns.

In code:

GRANT SELECT ON [dbo].[Employee] ([EmployeeId]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Name]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Address1]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Address2]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([City]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([State]) TO [Doctor];
GRANT SELECT ON [dbo].[Employee] ([Zip]) TO [Doctor];

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.

-- Cleanup code (drop the table & re-create 
-- it to get rid of existing permissions)
IF OBJECT_ID('Employee') > 0 
DROP TABLE Employee;
GO
CREATE TABLE Employee (
EmployeeId int NOT NULL IDENTITY (1,1),
Name varchar(255),
Address1 varchar(255),
Address2 varchar(255),
City varchar(255),
State varchar(255),
Zip varchar(50),
Salary money
);
GO

CREATE VIEW EmployeeList AS
SELECT 
EmployeeId, Name, Address1, Address2,
City, State, Zip
FROM Employee;

GRANT SELECT access to the view.

GRANT SELECT ON [dbo].[EmployeeList] TO [Doctor];

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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, object permissions, security