How To Create Encrypt Stored Procedure In SQL

In this article, we will learn how to create an encrypt Stored Procedure in SQL.

When you encrypt a stored procedure, the procedure’s text is converted to an obfuscated format and its definition is not visible for any users. Therefore, the procedure’s definition can not be viewed by users.

To create an encrypted stored procedure in SQL, you can use the CREATE PROCEDURE syntax and simply add the WITH ENCRYPTION argument just before AS statement.

Here’s an example for creating encrypt stored procedure,

CREATE PROCEDURE dbo.USP_GetEmployeeList
WITH ENCRYPTION
AS
BEGIN
  SELECT EmployeeCode,EmployeeName,EmployeePhone
  FROM dbo.Employees
END
GO

• Using object explorer

After creating the encrypted stored procedure, Let’s go-to object explorer and check whether you are able to modify the stored procedure or not.

You can see, modify option is disabled. so you can not make any changes in the stored procedure and that is the purpose of creating an encrypted stored procedure.

• Using sp_helptext

Now when You are using the sp_helptext stored procedure to view the procedure’s definition, you get a message it’s encrypted.

sp_helptext USP_GetEmployeeList

Result: The text for the object is encrypted.

• Using sys.sql_modules

if You are using sys.sql_modules to view the definition,

SELECT definition FROM sys.sql_modules WHERE OBJECT_ID = OBJECT_ID('dbo.USP_GetEmployeeList')

You can get the NULL result.

Execute a Stored Procedure

You can execute the encrypted stored procedure same as a regular stored procedure.

EXEC dbo.USP_GetEmployeeList 'TestCompany';

The same result returns for both stored procedures.

Encrypt Stored Procedure With Parameters

You can also create an encrypted stored procedure with parameters in the same way. Here’s an example,

CREATE PROCEDURE dbo.USP_GetEmployeeList
@CompanyName nvarchar(50)
WITH ENCRYPTION
AS
BEGIN
  SELECT EmployeeCode,EmployeeName,EmployeePhone
  FROM dbo.Employees
  WHERE CompanyName = @CompanyName
END
GO

It means that You can returns the data but you can not view the definition from anywhere and also can not modify stored procedure.

CAUTION!!!
Please make a backup of your stored procedures before encrypt because there is no turning around.
There are some third party tools that will decrypt but why should go in trouble.