SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Arup Chakraborty / True or False / Latest Posts

RE: True or False

SQLZealot — Wed, 29 Dec 2010 14:00:38 GMT

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' :)

RE: True or False

UMG Developer — Fri, 03 Dec 2010 13:36:26 GMT

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

RE: True or False

igsri — Mon, 29 Nov 2010 03:25:04 GMT

Many thanks to all of you..I was wrong, but now i understand..

RE: True or False

Yousaf Khan — Sun, 28 Nov 2010 22:49:44 GMT

Referencing to the bellow linkhttp://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/here the exampleCREATE PROCEDURE #RegularSPASSELECT TOP 10 CityFROM Person.AddressGO/* Create SP with Encryption */CREATE PROCEDURE #EncryptSPWITH ENCRYPTIONASSELECT TOP 10 CityFROM Person.AddressGO/* Execute SP - Execution Plan Tab shows up */EXEC #RegularSPGOif 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

RE: True or False

Yousaf Khan — Sun, 28 Nov 2010 22:45:12 GMT

Referencing to the link bellowhttp://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/if we create procedure like CREATE PROCEDURE #RegularSPASSELECT TOP 10 CityFROM Person.AddressGO/* Create SP with Encryption */CREATE PROCEDURE #EncryptSPWITH ENCRYPTIONASSELECT TOP 10 CityFROM Person.AddressGO/* Execute SP - Execution Plan Tab shows up */EXEC #RegularSPGOand execute the #RegularSP then it will show the execution planand if we execute the #EncryptSP then the execution will be disappear.

RE: True or False

Hardy21 — Sun, 28 Nov 2010 21:21:37 GMT

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.

RE: True or False

L' Eomot Inversé — Sun, 28 Nov 2010 05:48:51 GMT

[quote][b]igsri (11/27/2010)[/b][hr]Ii have created the procedure : CREATE PROC TestWITH ENCRYPTIONASSELECT *from tbl_TestNow this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.I have attached a screen shot also[/quote]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).

RE: True or False

Koen Verbeeck — Sat, 27 Nov 2010 05:42:47 GMT

[quote][b]igsri (11/27/2010)[/b][hr]Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.[/quote]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.

RE: True or False

igsri — Sat, 27 Nov 2010 05:24:23 GMT

Ii have created the procedure : CREATE PROC TestWITH ENCRYPTIONASSELECT *from tbl_TestNow this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.I have attached a screen shot also

RE: True or False

arup chakraborty — Sat, 27 Nov 2010 03:11:16 GMT

[quote][b]igsri (11/26/2010)[/b][hr]Good Question !!..We can see the Execution Plan of Encrypted Procedure.I have tested it..[/quote]Hi,Could u tell us how?

RE: True or False

SQLRNNR — Fri, 26 Nov 2010 08:51:37 GMT

Thanks for the question.

RE: True or False

SQLRNNR — Fri, 26 Nov 2010 08:51:14 GMT

[quote][b]Nakul Vachhrajani (11/26/2010)[/b][hr]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.([url=http://msdn.microsoft.com/en-us/library/ms187926.aspx]http://msdn.microsoft.com/en-us/library/ms187926.aspx[/url])Wouldn't we call viewing the execution plan a debugging exercise?[/quote]No. Not for the purposes of this question nor for the article referenced. I would call reading the execution plan a performance exercise.

RE: True or False

igsri — Fri, 26 Nov 2010 06:03:47 GMT

Good Question !!..We can see the Execution Plan of Encrypted Procedure.I have tested it..

RE: True or False

Phil Jackson — Fri, 26 Nov 2010 04:58:32 GMT

Good question! :-D

RE: True or False

Hardy21 — Fri, 26 Nov 2010 03:05:49 GMT

Really Nice question.

RE: True or False

Nakul Vachhrajani — Fri, 26 Nov 2010 02:25:42 GMT

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.([url=http://msdn.microsoft.com/en-us/library/ms187926.aspx]http://msdn.microsoft.com/en-us/library/ms187926.aspx[/url])Wouldn't we call viewing the execution plan a debugging exercise?

RE: True or False

sharath.chalamgari — Fri, 26 Nov 2010 02:15:21 GMT

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.

RE: True or False

Mayank Parmar — Fri, 26 Nov 2010 02:07:59 GMT

Hi,I never created any encrypted stored procedure. So I just guessed, any my guessing was correct. :-)Regards,Mayank Parmar

RE: True or False

Koen Verbeeck — Fri, 26 Nov 2010 01:01:17 GMT

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

RE: True or False

Rune Bivrin — Fri, 26 Nov 2010 00:48:33 GMT

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;-)

True or False

arup chakraborty — Thu, 25 Nov 2010 20:44:53 GMT

Comments posted to this topic are about the item [B]True or False[/B]