Learn more about SQL Server tools

   
   















































Filtering SQL Server Columns Using Column Level Permissions

MSSQLTips author K. Brian Kelley By:   |   Read Comments (4)   |   Related Tips: More > Security
Problem

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? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In this tip we cover how this can be done with column-level permissions.

Solution

One of the easiest ways to do this is through the use of views. However, if that isn't possible, there is another way: column-level permissions. They are a little harder to see and require a bit more diligence to keep track of, but they work just fine. So what's the difference between column level permissions and, say, table level permissions?

First, let's set up a table to use:

CREATE TABLE dbo.Employee (
EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(20) NOT NULL,
MiddleName VARCHAR(20) NULL,
SurName VARCHAR(20) NOT NULL,
SSN CHAR(9) NOT NULL,
Salary INT NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

And we'll go ahead and load it up with a couple of entries for a proof of concept:

INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary)
VALUES ('John', 'Mark', 'Doe', '111223333', 50000);
INSERT INTO dbo.Employee (FirstName, MiddleName, SurName, SSN, Salary)
VALUES ('Jane', 'Eyre', 'Doe', '222334444', 65000);

Let's go ahead and set up two users and two roles for this demonstration:

CREATE ROLE HR_Employee;
GO 
CREATE ROLE HR_Intern;
GO 
CREATE USER SalaryPerson WITHOUT LOGIN;
GO 
EXEC sp_addrolemember @membername = 'SalaryPerson', @rolename = 'HR_Employee';
GO 
CREATE USER SummerIntern WITHOUT LOGIN;
GO 
EXEC sp_addrolemember @membername = 'SummerIntern', @rolename = 'HR_Intern';
GO 

This sets up two levels of users: HR Employees (role HR_Employee, of which SalaryPerson is one) and HR Interns (role HR_Intern, played by SummerIntern). Now, when we normally grant permissions, we do so against the whole object or schema. For instance, this grants SELECT permission against the dbo.Employee table to HR_Employee role members:

GRANT SELECT ON dbo.Employee TO HR_Employee;

Now, we don't want interns to have this level of permissions. We only want them to have access to specific columns. There's a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:

GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;

Now, if you prefer the GUI, you can do and see the same thing in SQL Server Management Studio, it just takes a little closer eye. Note the difference between Figure 1 (a checkbox, signifying complete permissions against the table) and Figure 2 (a green square, indicating that there are some permissions, but we'll have to take a closer look).

using column level permissions

(Figure 1)

if you prefer the GUI, you can do and see the same thing in SQL Server Management Studio

(Figure 2)

If we click on the Select row, the button for Column Permissions activates. Clicking on that shows that we do indeed have permissions at the column level. Note there is no checkbox beside SSN nor Salary (Figure 3).

the button for Column Permissions activates

(Figure 3)

Therefore, the HR_Intern role cannot query these columns. They can find out that they are there, but they can't retrieve data. If you want to see these permissions in action, execute the following snippets. This should work just fine, because HR_Employees can SELECT against the whole table:

EXECUTE AS USER = 'SalaryPerson';
GO
SELECT * FROM dbo.Employee;
GO 
REVERT;
GO

This will fail with a couple of access denied errors, listing the columns the user cannot access:

EXECUTE AS USER = 'SummerIntern';
GO
SELECT * FROM dbo.Employee;
GO 
REVERT;
GO

The errors you should see:

Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column 'SSN' of the 
object 'Employee", database 'MSSQLTips', schema 'dbo'.
Msg 230, Level 14, State 1, Line 2
The SELECT permission was denied on the column 'Salary' of the 
object 'Employee", database 'MSSQLTips', schema 'dbo'.

This will work, because the columns in the query are accessible to HR_Intern:

EXECUTE AS USER = 'SummerIntern';
GO
SELECT EmployeeID, FirstName, SurName FROM dbo.Employee;
GO 
REVERT;
GO

And that's how to restrict using column permissions. Incidentally, you can do the same for DENY. Therefore, if a group of users already have access to columns they shouldn't, and you can't rework security in this manner, you could use DENY if you had to, like so:

DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;

Since DENY trumps any other permissions, this will effectively block access to those columns. This should be used as a last resort, obviously, because the use of DENY is not intuitive. And DENY at the column level is another step removed from what we're used to when looking at permissions.

Next Steps


Last Update: 9/30/2010


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, November 06, 2013 - 12:13:19 PM - subbu Read The Tip

DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;

 

error:-

 

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'Employee', because it does not exist or you do not have permission.

 
Regards,
Subbu

Tuesday, June 19, 2012 - 5:49:58 AM - Habib Read The Tip

Hi,

Is it possible to have a result with the column we have access ?

 

In this code:

EXECUTE AS USER = 'SummerIntern';
GO
SELECT * FROM dbo.Employee;
GO 
REVERT;
GO
Instead of having an error, is it possible to have the columns, the user (summerIntern) have access ?
 
Best Regards 
 

Monday, March 26, 2012 - 7:24:31 AM - D, Amartha Read The Tip

TSQL Is there. Sorry missed out reading in the first place.

Regards


Monday, March 26, 2012 - 7:23:28 AM - D, Amartha Read The Tip

Nice Article.

Can you please mention the versions of SQL Server on which this is applicable and also if you can share the TSQL Version of the UI alternative?

 

Regards




 
Sponsor Information