Filtering Columns in SQL Server Using Views

By:   |   Updated: 2010-09-21   |   Comments   |   Related: > Security

   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)


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?


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),
MiddleName VARCHAR(20) NULL,

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
SELECT EmployeeID, FirstName, MiddleName, SurName
FROM dbo.Employee;

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

we can grant SELECT permissions against the view and ensure the view only has the columns we want the users to query

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.

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

get scripts

next tip button

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

Article Last Updated: 2010-09-21

Comments For This Article


get free sql tips
agree to terms