How To Use Encrypted View In SQL Server

A VIEW is a virtual table defined by a query. VIEWs are essentially similar to other database objects whose structure along with query is stored in the database. VIEWs can have complex queries with multiple joins, nested views or ad-hoc queries. Most importantly, a VIEW is benefited because we can expose a limited number of fields to users. Allowing users to see fields defined in a VIEW instead of presenting them with actual tables stored in a database.

Assume few business users are using a VIEW to generate a financial report, but we don’t want them to be in a position of seeing or altering the created VIEW to generate the report. Users can use inbuilt metadata tables like sys.syscomments or sp_helptext to obtain the query behind a VIEW.

Adding a security layer, we can restrict users and other developers by creating ENCRYPTED VIEWs in SQL Server. To create an encrypted view in SQL Server by adding WITH ENCRYPTION option either while creating or altering the view.

1.

Advantages of Encrypted VIEWs

1.1 – Hides VIEW structure and content.
1.2 – The source code is unavailable for an encrypted view.

More vital to understand, once a VIEW is encrypted, no one (including the view owner) will be able to read the view’s structure or source code. Therefore, make sure to have view source code copied or stored in a repository before adding WITH ENCRYPTION option. This will help you to modify view structure in future if needed.

2.

How To Obtain VIEW Information

2.1 – Object explorer In SSMS
2.2 – sp_helptext – stored procedure
2.3 – sys.views and sys.syscomments – catalog views

Let’s create a table employee & a view v_employeeinfo. Here we will see, how to obtain view structure and its source code through a system created catalogue view and stored procedure.

USE tempdb;

IF OBJECT_ID(N'Employee', N'U') IS NOT NULL
DROP TABLE Employee;
GO

--Create Employee table
CREATE TABLE Employee
(
  ID    INT
 ,Name  VARCHAR(100)
);

INSERT dbo.Employee VALUES 
 (1, 'Atul')
,(2, 'Vishal')
,(3, 'Sangram');

IF OBJECT_ID(N'V_EmployeeInfo', N'V') IS NOT NULL
DROP VIEW V_EmployeeInfo;
GO

--Create a view
CREATE VIEW V_EmployeeInfo
AS 
SELECT ID, Name
FROM   Employee;
GO

--Query view
SELECT * 
FROM   V_EmployeeInfo;

Result Set

We will query the sys.syscomments catalogue view and execute the sp_helptext stored procedure to obtain v_employeeinfo view’s content. Revealing view’s source code and defined query.

sys.syscomments – Catalog View

Querying sys.syscomments view shows us the source code for the v_employeeinfo view.

USE tempdb;
GO

--Find view Source Code
SELECT TEXT 
FROM   sys.syscomments 
Where  ID = OBJECT_ID('V_EmployeeInfo','V');

Result Set

sp_helptext – stored procedure

Executing sp_helptext against view name shows the same source code for the v_employeeinfo view.

USE tempdb;
GO

--Find view Source Code using sp_helptext
sp_helptext 'V_EmployeeInfo'

Result set

3

Encrypting VIEWs

We want to alter the existing view so that the source code is encrypted and not readable. Let’s change the CREATE VIEW to ALTER VIEW and add WITH ENCRYPTION option before AS keyword.

USE tempdb;
GO
--Alter view add WITH ENCRYPTION Option
ALTER VIEW V_EmployeeInfo
WITH ENCRYPTION 
AS 
SELECT ID, Name
FROM   Employee;
GO

Always a best practice to query the view once we create or alter view with WITH ENCRYPTION option. This will confirm that it gives the expected results. Following query confirms that v_employeeinfo view is producing the similar results post-encryption.

Let’s query the sys.syscomments catalogue view and sp_helptext procedure, and see these system tables doesn’t reveal the view source code after adding encryption option.

3.1

sys.syscomments – Catalog View

Attempt to run sys.syscomments catalogue view returns NULL instead of view’s source code as an encryption option added to the view.

3.2

sp_helptext – Stored Procedure

The sp_helptext stored procedure returns an error message stating The text for object ‘V_EmployeeInfo’ is encrypted.

All the options are unavailable in SQL Server to obtain view’s source once WITH ENCRYPTION is added.

4.

Remove WITH ENCRYPTION Option

In the following query, we will alter the v_employeeinfo view and remove the WITH ENCRYPTION option clause to display the source code for the v_employeeinfo view as it was defined early.

USE tempdb;
GO
--Alter view to remove WITH ENCRYPTION Option
ALTER VIEW V_EmployeeInfo
AS 
SELECT ID, Name
FROM   Employee;
GO

Summary

In this tech-recipes post we’ve learned to encrypt a view in SQL Server so that its source code is not visible to anyone. If you like this post you may browse through Tech-Recipes Database Archive posts to learn more useful stuff.