SQL Server Hosting Tutorial : Encryption of Stored Procedures 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.

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.