Encryption of Stored Procedures in SQL Server

Introduction

The stored procedure can be encrypted. When you have complex business logics and want to hide the logic implementation then the schema of the stored procedure can be encrypted.

The sql server do not have any mechanism to decrypt the encrypted stored procedure. They have store manually.

CREATE PROCEDURE Proc_RetrieveProducts WITH 
ENCRYPTION AS 
BEGIN 
SET NOCOUNT ON
SELECT  ProductID,ProductName,ProductVendor     
FROM Products        
END

Once the stored procedure is compiled then it can be be viewed. The other stored procedure schema can be viewed on the following system table. But the above encrypted stored procedure text will be null.

SELECT
sc.id, so.name, sc.ctext, sc.text
FROM SYScomments sc
INNER JOIN sys.objects so ON so.object_id = sc.id

Normally the stored procedure schema will be viewed using the system stored procedure. The encrypted stored procedure will return the following message when try to view the schema.

The stored procedure cannot be viewed in the object explorer. The modify stored procedure option will be disabled.

Once stored procedure is created then the stored procedure should be manually stored some where in the local system and it should be used for the changes.