True or False

Question

True or False

arup chakraborty

Hall of Fame

Points: 3640
More actions
November 25, 2010 at 8:44 pm

#388312

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

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

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

Rune Bivrin SSCrazy Eights Points: 8515 More actions · Answer 1

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: 259115 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 10294 More actions · Answer 5

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

Hardy21

SSCrazy Eights

Points: 9708

November 26, 2010 at 3:05 am

#1255756

Really Nice question.

Thanks

Phil Jackson SSChasing Mays Points: 653 More actions · Answer 7

Good question! 😀

Phil

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

igsri SSC Veteran Points: 206 More actions · Answer 8

Good Question !!..

We can see the Execution Plan of Encrypted Procedure.

I have tested it..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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: 281334 More actions · Answer 10

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 More actions · Answer 11

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 Veteran Points: 206 More actions · Answer 12

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: 259115 More actions · Answer 13

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 More actions · Answer 14

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