Create a SQL Server view with encryption

How to encrypt a View in SQL Server using the create view statement and the encryption option?

How to create a SQL Server view with the encryption option? The WITH ENCRYPTION option allows you to hide the view code. Here is an example of code in T-SQL language to create a view with the ENCRYPTION option. First, here is the original tutorial with the script for creating the sales view used in the example.

SQL script to create a SQL Server view with encryption option

Use the following SQL code to create a SQL Server view with CREATE VIEW and the WITH ENCRYPTION option. This option hides the T-SQL code in the sys.syscomments system table. In the example below, create two views:

• The V_SALES view which is a classic view, i.e. without the encryption option
• The V_SALES_E view with the encryption option enabled.

CREATE VIEW [dbo].[V_SALES]
AS 
   SELECT [Year]
        ,[MonthName]
        ,[MonthCurrent]
        ,[NumberMonth]
        ,[EmployeeNumber]
        ,[NumberOfClients]
        ,[NumberOfSales]
        ,[Amount_ET]
        ,[Amount_IT]
     FROM [dbo].[SALES]; 

CREATE VIEW [dbo].[V_SALES_E]
WITH ENCRYPTION 
AS 
   SELECT [Year]
        ,[MonthName]
        ,[MonthCurrent]
        ,[NumberMonth]
        ,[EmployeeNumber]
        ,[NumberOfClients]
        ,[NumberOfSales]
        ,[Amount_ET]
        ,[Amount_IT]
     FROM [dbo].[SALES];

Thus the WITH ENCRYPTION option simply encrypts and hides the SQL code of the view in the sys.syscomments system table so that the view is not published if SQL Server replication is used. To verify that this option works, simply run a query on the sys.syscomments system table:

The code in the first view is clearly visible while the code in the second view with encryption enabled is not. Note: The option does not encrypt the data but the view code. There are SQL Server encryption functions to hide and encrypt sensitive data in MSSQL databases. To go further and discover views with aggregations and conditions, here is a tutorial to create SQL Server views with scripts.

Conclusion on SQL Server views with encryption

In conclusion, creating a SQL Server view with the WITH ENCRYPTION option is a useful technique to protect your view code from being published or copied without permission. By using this option, you can add an extra layer of security to your SQL Server database. In this article, we have provided you with a sample SQL script to create a view with encryption, as well as an explanation of the encryption principle. With this knowledge, you can now create your own encrypted views and protect your sensitive data.

Script to create a view in SQL Server