Create a SQL Server view with encryption

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 WITH ENCRYPTION option.

Create a SQL Server view with the encryption option

First, here is the original tutorial with the script for creating the sales view used in the example.

Setting up the encryption principle

Also, before we see the code for the view with encryption and its principle, what does encryption mean? Encryption, in the classical sense, means to hide data by replacing each letter with another letter. To do this, an encryption system uses two types of keys:

  • A public key (or one-way function) which only allows a message to be encrypted without decrypting it.
  • A private key which allows to decrypt a message.

So, for security reasons, communicate the public key and never the private key.

SQL script to create a view with the 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:

Script to create a MSSQL View with Encryption option
Script to create a MSSQL View with Encryption option

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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*