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.
Now when You are using the sp_helptext stored procedure to view the procedure’s definition, you get a message it’s encrypted.
Result: The text for the object is encrypted.
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.
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.