Track Stored Procedure Parameters After Execution in SQL Server

sql serverstored-procedures

I have been asked to identify a permissions issue with a stored procedure. This stored procedure behaves in two possible ways depending on what values are used for its parameters. 

exec ps_my_stored_procedure @a=1, @b=2, @c=3

is handled very differently from

exec ps_my_stored_procedure @a=5, @b=7, @c=0

You could say that ps_my_stored_procedure is divided logically into two completely separate processes.

Using dm_exec_procedure_stats and dm_exec_query_stats, I can find the execution plan which shows the SQL of the stored procedure used. I have not been able however, to recover how the parameters were defined and with what values.

Is it possible using dm_exec_procedure_stats and dm_exec_query_stats and any other management views to reconstruct the execution of the stored procedure that shows the values used for its parameters.

What I'd really like is to find in cache, is the actual execution of the stored procedure so that I can execute it as is using EXECUTE AS LOGIN = 'someone' to resolve the permissions issues

Best Answer

If you have the execution plan you can view it as xml, and search for compiled parameter values

For example, this is an actual snippet of an execution plan from one of the queries in my plan cache:

   <ParameterList>
      <ColumnReference Column="@P69" ParameterCompiledValue="'2015-06-10 00:00:00.0000000'" />
       <ColumnReference Column="@P68" ParameterCompiledValue="'somestring'" />
   </ParameterList>

If your plans get reused it will show the value at the time of compilation though.

Related Question