Profiler Execution Plan and Encrypted SP''s

• Ian-386680

  Mr or Mrs. 500

  Points: 509

  More actions

  March 13, 2007 at 10:44 am

  #76672

  Hi,

  Is it possible to get Query Analyzer to display the execution plan for encrypted stored procedures?

  ian

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  March 13, 2007 at 4:50 pm

  #694837

  I don't think it is - it's a couple of years since I did this - seem to remember I decrypted the procs on  a test db. I think you can display the query plan though.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 14, 2007 at 7:16 am

  #694921

  i just tested this, and it looks like you can see the execution plan;

  i made a crappy proc, and tested it with encryption and without; both times i could see the execution plan:

  --for users who are too lazy to type "SELECT * FROM"   

  ALTER procedure sp_show    

  --USAGE: sp_show tbcounty   

  @TblName varchar(128)   

  WITH ENCRYPTION   

  As   

  Begin   

   exec('Select * from ' + @TblName)   

  End

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Clive Strong

  SSChampion

  Points: 11222

  More actions

  March 14, 2007 at 8:23 am

  #694959

  Yup, it's possible.

  We have a 3rd party tool which has all of the stored procedures encrypted.  Aside from finding a decryption tool, it was my resource to finding dependent tables, as sp_depends would not show me this information.