Filtering Columns in SQL Server Using Views

By: K. Brian Kelley   |   Updated: 2010-09-21   |   Comments   |   Related: > 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?

Solution

One of the easiest ways to do this is through the use of views, however, there are two requirements for this:

1. The users do not already have the ability to select data from the table.
2. A view can be created where an ownership chain can be established with the table.

If you're not familiar with ownership chaining, you can read more about it in this previous tip.

If the users don't already have access to the table, we can grant SELECT permissions against the view and ensure the view only has the columns we want the users to query. For instance, consider the following sample table:

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);

Now let's say that we have a certain subset of users who should never see the salary amount and social security number for employees. We can create a view which only includes the columns which we want to display. For instance:

CREATE VIEW dbo.FilteredEmployee
AS
SELECT EmployeeID, FirstName, MiddleName, SurName
FROM dbo.Employee;

A SELECT * from this view shows the columns which are available:

If we try to force a SELECT against columns which aren't included in the view, we'll get an error. For instance, this SELECT statement will fail:

SELECT EmployeeID, Salary
FROM dbo.FilteredEmployee;

Therefore, if we can use a view, we can successfully filter the data and grant the users access to the view, we can present just the data we want the users to have access to.

Next Steps

• Views can also be used used for INSERTs, UPDATEs and DELETEs, but it would be better to have users use stored procedures for data maniuplation
• Once the view is created you would just GRANT select rights to the user or the database role that you setup. This can be done using the GRANT command or using SQL Server Management Studio.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips