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:
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.