True or False

  • arup chakraborty

    Hall of Fame

    Points: 3640

    Comments posted to this topic are about the item True or False

  • Rune Bivrin

    SSCertifiable

    Points: 7833

    Great question.

    But the version should have been specified. In SQL 2000 you can see the execution plan even for encrypted procs. Although that would probably have been a dead giveaway of the correct answer;-)


    Just because you're right doesn't mean everybody else is wrong.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Great question! You've got me thinking for a while. Definately learned something.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Mayank Parmar

    Ten Centuries

    Points: 1019

    Hi,

    I never created any encrypted stored procedure. So I just guessed, any my guessing was correct. 🙂

    Regards,

    Mayank Parmar

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    Good Question, in our development we have only one Proc with encryption and i was doing some R&D on it, it helped me to answer this Question.

  • Nakul Vachhrajani

    SSChampion

    Points: 10221

    The question missed the version of SQL Server targetted, however, this behaviour is still confusing.

    From books online:

    However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.

    (http://msdn.microsoft.com/en-us/library/ms187926.aspx)

    Wouldn't we call viewing the execution plan a debugging exercise?

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Really Nice question.

    Thanks

  • Phil Jackson

    SSChasing Mays

    Points: 653

    Good question! 😀

    Phil

    Although all answers are replies, not all replies are answers.
    Blog: http://philjax.wordpress.com

  • igsri

    SSC Enthusiast

    Points: 176

    Good Question !!..

    We can see the Execution Plan of Encrypted Procedure.

    I have tested it..

  • SQLRNNR

    SSC Guru

    Points: 281243

    Nakul Vachhrajani (11/26/2010)


    The question missed the version of SQL Server targetted, however, this behaviour is still confusing.

    From books online:

    However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.

    (http://msdn.microsoft.com/en-us/library/ms187926.aspx)

    Wouldn't we call viewing the execution plan a debugging exercise?

    No. Not for the purposes of this question nor for the article referenced. I would call reading the execution plan a performance exercise.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • arup chakraborty

    Hall of Fame

    Points: 3640

    igsri (11/26/2010)


    Good Question !!..

    We can see the Execution Plan of Encrypted Procedure.

    I have tested it..

    Hi,

    Could u tell us how?

  • igsri

    SSC Enthusiast

    Points: 176

    Ii have created the procedure :

    CREATE PROC Test

    WITH ENCRYPTION

    AS

    SELECT *from tbl_Test

    Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.

    I have attached a screen shot also

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    igsri (11/27/2010)


    Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.

    Yes, you see an execution plan that says you are executing a stored procedure.

    But do you see the execution plan of what is inside the stored procedure? The actual steps that the sp performs?

    No you don't, and that's because the sp is encrypted. If it wasn't, you'd see a lot more.

    Try and create the same sp without encryption and then ask for the execution plan, you'll see.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • TomThomson

    SSC Guru

    Points: 104773

    igsri (11/27/2010)


    Ii have created the procedure :

    CREATE PROC Test

    WITH ENCRYPTION

    AS

    SELECT *from tbl_Test

    Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.

    I have attached a screen shot also

    The screen shot doesn't show an execution for the stored procedure: it shows the execution plan for a batch which calls the stored procedure, with the execution of teh stored procedure itself omitted (because the SP was encrypted).

    Tom

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply