Encrypted stored procedure is encrypting the plan cache query plan

Question

I'm having a really bizarre issue when querying the plan cache that relates to stored procedure encryption. However, it's the stored procedure that is querying the plan cache that is encrypted, not the queries in the plan cache themselves. Let me explain...

The procedure below simply searches the plan cache for a matching string and outputs the query plan (the last column).

    ALTER PROCEDURE spQueryPlanCache @textstring nvarchar(max)
    AS
    BEGIN
        SELECT 
                databases.name,
            dm_exec_sql_text.text AS TSQL_Text,
            dm_exec_query_stats.creation_time, 
            dm_exec_query_stats.execution_count,
            dm_exec_query_stats.total_worker_time AS total_cpu_time,
            dm_exec_query_stats.total_elapsed_time, 
            dm_exec_query_stats.total_logical_reads, 
            dm_exec_query_stats.total_physical_reads, 
            dm_exec_query_plan.query_plan
        FROM sys.dm_exec_query_stats 
        CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
        CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
        INNER JOIN sys.databases
        ON dm_exec_sql_text.dbid = databases.database_id
        WHERE dm_exec_sql_text.text LIKE @textstring
        
    END

This is a sample of the plan XML that is outputted in the last column:

 <StmtSimple StatementText="CREATE PROCEDURE [dbo].[spMySproc];1 (@...

Everything looks normal - my plan cache sproc has found a procedure called spMySproc in the plan cache and I can see the statement text. Great!

However.....if I add WITH ENCRYPTION to the definition of my plan cache query procedure (spQueryPlanCache) and re-run it, the query_plan column from the plan cache that is OUTPUTTED by my encrypted stored procedure has itself been encrypted!!!

  <StmtSimple StatementText="** Encrypted Text **" 

This makes no sense to me. The only circumstance where I would expect this to happen is if spMySproc was also encrypted, which it isn't! If I query the plan cache again, I can see that spMySproc is not encrypted.

Is this a known bug, or can someone explain why this makes sense to them?

Here is a minimal, complete, and verifiable example showing the issue:

CREATE OR ALTER PROCEDURE dbo.test_proc
AS
BEGIN
    SELECT 0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D;
END
GO
CREATE OR ALTER PROCEDURE dbo.spQueryPlanCache @textstring nvarchar(max)
WITH ENCRYPTION
AS
BEGIN
    SELECT deqp.query_plan
    FROM sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
    WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
        AND deqp.query_plan IS NOT NULL;
END
GO

--output from this will contain the query plan, with the StatementText column "encrypted".
EXEC dbo.spQueryPlanCache @textstring = '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%';

GO
CREATE OR ALTER PROCEDURE dbo.test_enc
(
    @xml nvarchar(max)
)
WITH ENCRYPTION
AS
BEGIN
    SELECT CONVERT(xml, @xml);
END
GO

DECLARE @val nvarchar(max);

SELECT TOP(1)
    @val = CONVERT(nvarchar(max), deqp.query_plan)
FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
    AND deqp.query_plan IS NOT NULL;

--output here will show the same query plan, but the STatementText won't be encrypted.
EXEC dbo.test_enc @val;

The same behavior has been exhibited on 2014, 2016, and 2019.

Answer 1

The behavior you are observing is expected and is not a bug. When you add the WITH ENCRYPTION option to a stored procedure, it encrypts the stored procedure's definition, including the query text. As a result, any output from that encrypted stored procedure, such as the query_plan column in your case, will also be encrypted.

To further clarify, the encryption is applied to the stored procedure's definition and not the individual queries in the plan cache. The plan cache stores the compiled plans, but when the encrypted stored procedure is executed, it will decrypt the definition and generate the query plan dynamically during execution. However, the actual query text within the plan is still encrypted.

In your case, when you execute the spQueryPlanCache stored procedure, which has the WITH ENCRYPTION option, the query plan returned will have the encrypted statement text because the stored procedure itself is encrypted. This behavior is by design to protect the encrypted stored procedure's definition from being exposed.

If you need to access the query plan without encryption, you would need to remove the WITH ENCRYPTION option from the spQueryPlanCache stored procedure or use a separate non-encrypted stored procedure to retrieve the query plans.

Here's an updated version of the script without encryption:

CREATE OR ALTER PROCEDURE dbo.spQueryPlanCache @textstring nvarchar(max)
AS
BEGIN
    SELECT deqp.query_plan
    FROM sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
    WHERE dest.text LIKE '%' + @textstring + '%'
        AND deqp.query_plan IS NOT NULL;
END