Filtering SQL Server Columns Using Column level Permissions

Problem

I have a table where some of the columns should is queryable by all users. How can I filter the data appropriately so and not everyone can select the data? In a previous tip, the Filtering Columns in SQL Server with the using views we looked at the using views. In this tip we cover the can is done with Column-level permissions.

Solution

One of the easiest ways to does this is through. However, if that isn ' t possible, there is another way:column-level permissions. They is 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 (), FirstName varchar (20) Not null,middlename varchar (null,surname varchar) Not NULL,SSN CHAR (9) isn't 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.E Mployee (FirstName, MiddleName, SurName, SSN, Salary) VALUES (' Jane ', ' Eyre ', ' Doe ', ' 222334444 ', 65000);

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

This sets up the levels of USERS:HR Employees (role Hr_employee, of which Salaryperson is one) and HR interns (role hr_in Tern, played by Summerintern). Now, when we normally grant permissions, we did 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 do not want interns to any of the permissions. We only want them to has access to specific columns. There ' s a-to-do. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do t HAT) 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 Clos ER eye. Note the difference between Figure 1 (a checkbox, signifying complete permissions against the table) and Figure 2 (a green Square, indicating that there is some permissions, but we'll have an closer look for take a.

(Figure 1)

(Figure 2)

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

(Figure 3)

Therefore, the Hr_intern role cannot query these columns. They can find out that they is 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 '; Goselect * FROM dbo. Employee; GO REVERT; GO

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

EXECUTE as USER = ' summerintern '; Goselect * FROM dbo. Employee; GO REVERT; GO

The errors should see:

MSG, level, State 1, line 2The SELECT permission is denied on the column ' SSN ' of the object ' Employee ', database ' Mssqltips ', schema ' dbo '. MSG, level, State 1, line 2The SELECT permission is denied on the column ' Salary ' of the object ' Employee ', Databa Se ' mssqltips ', schema ' dbo '.

This would work, because the columns in the query is accessible to Hr_intern:

EXECUTE as USER = ' summerintern '; Goselect 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 has access to columns they shouldn ' t, and your can ' t rework security in this manner , you could use the DENY if you had to, like so:

DENY SELECT on dbo. Employee (SSN, Salary) to Hr_intern;

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

Filtering SQL Server Columns Using Column level Permissions