Microsoft SQL Server 2012 Bible

Locking Down the View

Views are designed to control access to data. Several options protect the data or the view.

The WITH CHECK OPTION causes the WHERE clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved. In a sense, it makes the WHERE clause a two-way restriction.

The WITH CHECK OPTION is useful when the view should limit inserts and updates with the same restrictions applied to the WHERE clause.

Unchecked Data

To understand the need for the WITH CHECK OPTION, you must first understand how views function without the CHECK OPTION. The following view generates a list of Product SubCategories for the Bikes Product Category:

USE AdventureWorks2008R2
GO
CREATE view vComponentsProductSubCats
AS
SELECT 
  ProductCategoryID,
  Name ProductSubCategory
FROM Production.ProductSubcategory
WHERE ProductCategoryID = 1;
GO
SELECT ProductCategoryID, ProductSubCategory FROM 
  dbo.vComponentsProductSubCats
Results:
ProductCategoryID  ProductSubCatgory
------------------- ---------------------
1          Mountain Bikes
1          Road Bikes
1          Touring Bikes

If someone adds a Bike Pedal Accessory and inserts using the view without the CHECK OPTION, the INSERT is permitted.

INSERT INTO vComponentsProductSubCats(ProductCategoryID, 
  ProductSubCategory)
VALUES(2, ‘Bike Pedal');
(1 row(s) affected)

The INSERT worked, and the new row is in the database, but the row is not visible through the view because the WHERE clause of the view filters out the inserted row. ...