SQL SERVER – Stored Procedure WITH ENCRYPTION and Execution Plan

Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP are encrypted it is not possible to get original text of the SP from SP itself. User who created SP will need to save the text to be used to create SP somewhere safe to reuse it again.

Interesting observation:
What prompted me to write this article is when SP are encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command and does not display the tab of execution plan if only Encrypted SP is called.

To understand this scenario let us create two SPs. First without encryption and second with encryption. Once SPs are created run them one by one with having Actual Execution Plan turned on. To learn how to turn on Actual Execution Plan read my previous article SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan.

/* Create SP without Encryption */
CREATE PROCEDURE #RegularSP
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Create SP with Encryption */
CREATE PROCEDURE #EncryptSP
WITH ENCRYPTION
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Execute SP - Execution Plan Tab shows up */
EXEC #RegularSP
GO


/* Execute SP - Execution Plan Tab does not shows up */
EXEC #EncryptSP
GO

/* Clean Up */
DROP PROCEDURE #RegularSP
DROP PROCEDURE #EncryptSP
GO

It is very clear from above example that Encrypted SP does not show the actual execution plan as from Actual Execution Plan user can figure out the logic behind the SP. To avoid this issue for encrypted SP Management Studio does not display the execution plan.

Reference : Pinal Dave (http://www.SQLAuthority.com)