How To Encrypt SQL Server Stored Procedures, Views and Functions

Stored procedures, functions, and views are objects that each application uses at the database tier. In some cases, it may be necessary to encrypt the text parts of these objects for various reasons. Let’s look at how to encrypt these three types of objects through an example.

In the AdventureWorks2014 database, we get the create script of the HumanResources.vEmployee view as follows.

After the CREATE VIEW section, we add WITH ENCRYPTION as follows. I added Encrypted to the end of the view name so that the view’s name is different from the original.

You may receive an error as follows when you run the script.

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 84

Property cannot be added. Property ‘MS_Description’ already exists for ‘HumanResources.vEmployee’.

I will not mention it because it is irrelevant to our subject. Even though it receives an error, you will see that the encrypted version of the view is created as follows.

As I explained at the beginning of the article, when you try to get the script of this view, it will give you an error as follows because the content of the view is encrypted.

Property TextHeader is not available for View ”. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

You can also perform this procedure for Stored Procedures and Functions. You must put the WITH ENCRYPTION statement between the CREATE and AS statements.

Important Note: Before encrypting objects, I recommend you to make a copy, as you may need to update them later.