Stored Procedures are a crucial component of many applications as they contain the business logic that drives the application’s functionality. However, there may be instances where it is necessary to hide this business logic from end users for security or other reasons. In SQL Server, the keyword “WITH ENCRYPTION” is used to encrypt the text of a Stored Procedure, making it impossible to retrieve the original code from the procedure itself.

One interesting observation is that when a Stored Procedure is encrypted, the Actual Execution Plan of the procedure is also not visible. In fact, attempting to view the execution plan of an encrypted Stored Procedure does not display any information. To better understand this scenario, let’s create two Stored Procedures – one without encryption and one with encryption – and observe their behavior when executing them with the Actual Execution Plan turned on.

First, let’s create a regular Stored Procedure without encryption:

CREATE PROCEDURE RegularSP
AS
SELECT TOP 10 City FROM Person.Address
GO

Next, let’s create a Stored Procedure with encryption:

CREATE PROCEDURE EncryptSP WITH ENCRYPTION
AS
SELECT TOP 10 City FROM Person.Address
GO

Now, let’s execute both Stored Procedures and observe the behavior of the Actual Execution Plan:

EXEC RegularSP
GO

EXEC EncryptSP
GO

As you can see, when executing the regular Stored Procedure, the Execution Plan tab shows up and we can view the actual execution plan. However, when executing the encrypted Stored Procedure, the Execution Plan tab does not appear, preventing us from accessing the execution plan.

This behavior is intentional to protect the logic behind the encrypted Stored Procedure. By not displaying the execution plan, users are unable to analyze the plan and potentially reverse engineer the business logic.

It is important to note that if you need to reuse the encrypted Stored Procedure, the user who created it must save the encrypted text somewhere safe for future use.

Overall, understanding encrypted Stored Procedures in SQL Server is crucial for protecting sensitive business logic. By utilizing the “WITH ENCRYPTION” keyword, you can ensure that the code within your Stored Procedures remains secure and inaccessible to unauthorized users.

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