Encrypt the stored procedure in sql server

In this article, I will explain about how to encrypt a stored procedure in SQL Server.

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.

SP1.jpg

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.

SP2.jpg

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

SP3.jpg


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.