True or False

  • Hardy21

    SSCrazy Eights

    Points: 9708

    We are not able to see the execution plan of encrypted stored procedure, if that is the case then there is the need of encryption keyword 🙂

    If we use SQL Profiler and add performance -> "Showplan XML" or "Showplan text", then profiler displays "Encrypted text" when it is executing actual procedure.

    Thanks

  • Yousaf Khan

    Ten Centuries

    Points: 1147

    Referencing to the link bellow

    http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

    if we create procedure like

    CREATE PROCEDURE #RegularSP

    AS

    SELECT TOP 10 City

    FROM Person.Address

    GO

    /* Create SP with Encryption */

    CREATE PROCEDURE #EncryptSP

    WITH ENCRYPTION

    AS

    SELECT TOP 10 City

    FROM Person.Address

    GO

    /* Execute SP - Execution Plan Tab shows up */

    EXEC #RegularSP

    GO

    and execute the #RegularSP then it will show the execution plan

    and if we execute the #EncryptSP then the execution will be disappear.

  • Yousaf Khan

    Ten Centuries

    Points: 1147

    Referencing to the bellow link

    http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

    here the example

    CREATE PROCEDURE #RegularSP

    AS

    SELECT TOP 10 City

    FROM Person.Address

    GO

    /* Create SP with Encryption */

    CREATE PROCEDURE #EncryptSP

    WITH ENCRYPTION

    AS

    SELECT TOP 10 City

    FROM Person.Address

    GO

    /* Execute SP - Execution Plan Tab shows up */

    EXEC #RegularSP

    GO

    if create the stored procedure like above and execute the #RegularSP then it will show the executio plan and if we execute the #EncryptSP then the execution plan will be disappear.

    Yousaf Khan

  • igsri

    SSC Enthusiast

    Points: 176

    Many thanks to all of you..

    I was wrong, but now i understand..

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question, it made me think about it some and learn something. (Though I still don't think encrypted procedures are worthwhile.)

  • SQLZealot

    Valued Member

    Points: 63

    Nice question!

    I was tricked by the 'Estimated execution plan' and 'Actual Execution Plan'.

    You definitely don't see the Actual Execution Plan for encrypted stored procs. However, you can see 'something' in the estimated execution plan, which is not relevant, but still...enough for me to rush and press 'Yes' 🙂

Viewing 6 posts - 16 through 21 (of 21 total)

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