Encrypting Stored Procedure, View & Function in SQL Server

You may come across situation where you have to hide or encrypt stored procedures, views and user defined functions in SQL Server. For example, you are writing a stored procedure having some proprietary business logic. The business logic may need to be hidden from the plain sight. In such a situation you need to go for encrypting stored procedure or the view or the UDF and hide the code.

For hiding the source code of the stored procedure or view or function, you can use the WITH ENCRYPTION option along with CREATE script to encrypt the object hide it from the plain sight. I wont say this as a secured method to encrypt the stored procedure. Nowadays there are a lot of third-party tools available in internet to decrypt the SQL objects. Using WITH ENCRYPTION is just a basic level of hiding the source code of the programmable objects like procedures, views and functions. Do not use this option to hide highly confidential source codes.

Once encrypted, there is no straight forward way in SQL Server to decrypted the procedure. You may need to search the web to find third-party tools for decrypting it. So, keep the source code of the object in a source control before running the CREATE object script with encrypt option.

Now, let’s see how to encrypt the stored procedures, views and user defined functions.

Encrypting Stored Procedure

To encrypt a stored procedure you just need to use the WITH ENCRYPTION option along with the CREATE PROCEDURE script. Below is an example of creating stored procedures one with encryption and another without encryption.

Let’s create a regular store procedure:

CREATE PROCEDURE MTB_NoEncryption
AS
BEGIN
	SELECT 'From MTB_NoEncryption'
END
GO

Lets create another stored procedure WITH ENCRYPTION

CREATE PROCEDURE MTB_WithEncryption WITH ENCRYPTION
AS
BEGIN
	-- START: Properitary Business Logic
	--
	--
	--
	--
	-- END: Properitary Business Logic
	
	SELECT 'From ENCRYPTED Stored Procedure'
END
GO

If you execute both the stored procedures, they will generate the expected results

Now, run sp_helptext for the stored procedures to see the source code of the procedure. You will not be able to see the code of the encrypted stored procedure. Instead of the code you will see a message “The text for object ‘MTB_WithEncryption’ is encrypted.“.

If you try to create a script for the encrypted stored procedure from the Object Explorer in Management Studio, you will get a pop-up error message box.

Now try to get the ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES. You will see NULL in the ROUTINE_DEFINITION field.

Run the SQL Server Profiler trace with SP:StmtStarting and SP:StmtEnding events selected. Run the encrypted stored procedure and check the profiler trace. Notice the TextData value against SP:StmtStarting and SP:StmtEnding events shows “– Encrypted text”.

Encrypting Function (UDF)

Now, we’ll see how to encrypt or hide the code of a user defined function. Just like a stored procedure, to encrypt a function you just need to use the WITH ENCRYPTION option along with the CREATE FUNCTION script. Below is an example of creating functions one with encryption and another without encryption.

Let’s create a regular function:

CREATE FUNCTION MTB_fnNoEncryption
(
	@value varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
	
	RETURN @value + ' - From Regular Function'

END
GO

Now, create another function with ENCRYPTION:

CREATE FUNCTION MTB_fnWithEncryption
(
	@value varchar(50)
) 
RETURNS varchar(50) WITH ENCRYPTION
AS
BEGIN
	-- START: Properitary Business Logic
	--
	--
	--
	--
	-- END: Properitary Business Logic
	RETURN @value + ' - From ENCRYPTED Function'

END
GO

Try to see the both these functions code using sp_helptext. Just like the encrypted stored procedure you can see the code of the encrypted function is not displayed.

Similarly try seeing the code of the encrypted user defined function from object explorer in management studio or using the INFORMATION_SCHEMA.ROUTINES. You cannot see the code.

Encrypting Views

Similar to encrypting stored procedure and function, you can encrypt views as well. You just need to add WITH ENCRYPTION in the create view statement.

CREATE VIEW TestView WITH ENCRYPTION
AS
select * from Application.Countries
Go

Pros & Cons of Using WITH ENCRYPTION

Pros

• This is one among the easiest method to hide the code from plain sight.
• You can hide low security information or propriety source code in the stored procedures views and user defined functions.

Cons

• The encrypted SQL objects could be easily decrypted. There are several tools available in the internet to decrypt. So it’s not advisable to depend on this technique to hide highly confidential information or code.

Related Articles

• Escaping single quotes in string variables.

Reference

• Creating stored procedure with encryption at MSDN.
• Creating function with encryption at MSDN.