True or False

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

  • 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.

  • 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

  • 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
    --------------------------------------------------------------------------------

  • 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.

  • 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

    Follow me on
    Twitter: @sqltwins

  • Really Nice question.

    Thanks

  • Good question! 😀

    Phil

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

  • Good Question !!..

    We can see the Execution Plan of Encrypted Procedure.

    I have tested it..

  • 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

  • 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

  • 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?

  • 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

  • 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

  • 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 20 total)

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