Views with Check Encryption and Schema Binding Options in SQL Server

Check Encryption and Schema Binding Options in SQL Server Views

In this article, I am going to discuss how to use the Check Encryption and Schema Binding Options in SQL Server Views with examples. Please read our previous article before proceeding to this article where we discussed the Complex Views in SQL Server with examples.

Let us understand how to use the Check Encryption and Schema Binding Options in SQL Server Views with an example:

We are going to use the following Department and Employee table to understand how to use the Check Encryption and Schema Binding Options in SQL Server Views.

how to use the Check Encryption and Schema Binding Options in SQL Server Views

Please use the below SQL Script to create and populate the Department and Employee table with some test data.

-- Create Department Table
CREATE TABLE Department
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50)
)
GO

-- Populate the Department Table with test data
INSERT INTO Department VALUES(1, 'IT')
INSERT INTO Department VALUES(2, 'HR')
INSERT INTO Department VALUES(3, 'Sales')

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  Salary DECIMAL(18,2),
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 25000, 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 30000, 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060',40000, 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 35000, 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 27000, 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 33000, 2)
GO
With check option in SQL Server Views:

In SQL Server, if a view is created by using a where condition and later on if any modification is performed on that view against the where condition still those changes are accepted.

Let us understand this with an example.

Example: Create a View which will retrieve the IT department employees from the Employee table

CREATE VIEW vwITDepartmentEmployees 
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1

In the above example, the view vwITDepartmentEmployees is created with a where condition i.e. “DeptID = 1” but if we trying to insert a record that violates the where condition, still that operation gets performed.

Let’s try to insert one record other than the DeptID 1.

INSERT INTO vwITDepartmentEmployees (ID, Name, Gender, DOB, Salary, DeptID)

VALUES(7, ‘Rohit’, ‘Male’,’1994-07-24 10:53:27.060′, 45000, 2)

And when we execute the above update statement the data gets inserted into the Employee table without any error. To see the data make a Select query against the Employee table as shown below.

SELECT ID, Name, Gender, DOB, Salary, DeptID FROM Employee

It will give us the below result set

how to use the Check Encryption and Schema Binding Options in SQL Server Views

As you can see the new records is inserted into the Employee table.

If you want to restrict any DML operations on the view against the where condition, then you need to add “WITH CHECK OPTION” either at the time of creating the view or we can add that option by altering the view.

Alter the view vwITDepartmentEmployees to add the WITH CHECK OPTION as shown below
ALTER VIEW vwITDepartmentEmployees 
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Now try to insert a record which violates the where condition and sees what happens

INSERT INTO vwITDepartmentEmployees (ID, Name, Gender, DOB, Salary, DeptID)

VALUES(8, ‘Mahesh’, ‘Male’,’1994-07-24 10:53:27.060′, 55000, 2)

Now when we try to execute the above Insert statement, the execution fails as the where condition of the view is violating and it will give the following error

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

With Encryption in SQL Server Views:

After creating a view we can see the text of the view that we have written under the text column of the syscomments table. But for any security reason if we don’t want to disclose that information to anyone then you can hide it by using the “WITH ENCRYPTION” option either while creating the view or altering the view.

You can see the text of your view under the text column of the syscomments table as shown below.

SELECT id, ctext, text FROM 
SYSCOMMENTS 
WHERE ID = OBJECT_ID('vwITDepartmentEmployees')

Once you use the “With Encryption” option then you cannot view the text of the view using the sp_helptext system stored procedure. If you try to view the text using the sp_helptext system stored procedure, then we will get a message stating ‘the text for the object is encrypted’.

The sp_helptext system procedure actually retrieve the text from the syscomments table (text column of the syscomments table which actually stores the text of our view)

Here you can see the text of the view vwITDepartmentEmployees using the sp_helptext system defined Stored Procedure as shown below.

Views with Check Encryption and Schema Binding Options in SQL Server

Now let’s modify the view to use the WITH ENCRYPTION option as shown below.
ALTER VIEW vwITDepartmentEmployees 
WITH ENCRYPTION
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Now if you verify the text of our view under the text column of the syscomments table, it will show null as shown below.

SELECT id, ctext, text FROM 
SYSCOMMENTS 
WHERE ID = OBJECT_ID('vwITDepartmentEmployees')

And if you use the sp_helptext system stored procedure to view the text it will give you the following error message

Views with Check Encryption and Schema Binding Options in SQL Server

With SCHEMABINDING Option in SQL Server Views:

The SCHEMABINDING option specifies that the View is bound to the database objects that it references. So, when the SCHEMABINDING option is specified the database object cannot be modified in any way that would affect the View definition. The View definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

So in the simple word we can say that, if a view is created by using the attribute “SCHEMABINDING” then we cannot drop or alter the table columns on which the view is dependent.

Suppose we create a View that depends on the table Employee and after creating the View if we modify the table from the database while the View is referencing to that table. After modification of the table if we execute the View it will give an error message that isInvalid Object Name Employee

Let’s modify the vwITDepartmentEmployees view to use the SCHEMABINDING option as shown below
ALTER VIEW vwITDepartmentEmployees 
WITH SCHEMABINDING
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM dbo.Employee 
WHERE DeptID = 1
WITH CHECK OPTION

Note: When you want to use the SCHEMABINDING option, then you must have to specify each and every column name in the select statement but cannot use “*”. Moreover while using the SCHEMABINDING option the table name must be prefixed with the owner name i.e. dbo which tells the current user is only the owner of the table.

Now let’s try to delete the Employee table as shown below.

DROP TABLE Employee

When we execute the above statement, it will give us the error as Cannot DROP TABLE ‘Employee’ because it is being referenced by object ‘vwITDepartmentEmployees’.

If required we can use the “WITH ENCRYPTION” and “WITH SCHEMABINDING” option at the same time as shown below.

ALTER VIEW vwITDepartmentEmployees 
WITH ENCRYPTION, SCHEMABINDING
AS 
SELECT ID, Name, Gender, DOB, Salary, DeptID 
FROM dbo.Employee 
WHERE DeptID = 1
WITH CHECK OPTION

In the next article, I will discuss Indexed Views in SQL Server with examples.

SUMMARY

In this article, I try to explain how to use the Check Encryption and Schema Binding Options in SQL Server Views step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *