Decryptencryptedstoredprocedures

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.html

---

ThisSPwilldecryptStoredProcedures,ViewsorTriggersthatwereencryptedusing"withencryption".ItisadaptedfromascriptbyJosephGamaandShoeBoy.Therearetwoversions:oneforSP'sonlyandtheotheroneforSP's,triggersandviews.Forversion1,theinputisobjectname(storedprocedure,viewortrigger),andforversion2,theinputisobjectname(storedprocedure,viewortrigger),objecttype('T'-trigger,'P'-storedprocedureor'V'-view).FromPlanetSourceCode.com.

createPROCEDUREsp_decrypt_sp(@objectNamevarchar(50))ASDECLARE@OrigSpText1nvarchar(4000),@OrigSpText2nvarchar(4000),@OrigSpText3nvarchar(4000),@resultspnvarchar(4000)declare@iint,@tbigint--getencrypteddataSET@OrigSpText1=(SELECTctextFROMsyscommentsWHEREid=object_id(@objectName))SET@OrigSpText2='ALTERPROCEDURE'+@objectName+'WITHENCRYPTIONAS'+REPLICATE('-',3938)EXECUTE(@OrigSpText2)SET@OrigSpText3=(SELECTctextFROMsyscommentsWHEREid=object_id(@objectName))SET@OrigSpText2='CREATEPROCEDURE'+@objectName+'WITHENCRYPTIONAS'+REPLICATE('-',4000-62)--startcounterSET@i=1--filltemporaryvariableSET@resultsp=replicate(N'A',(datalength(@OrigSpText1)/2))--loopWHILE@i<=datalength(@OrigSpText1)/2BEGIN--reverseencryption(XORoriginal+bogus+bogusencrypted)SET@resultsp=stuff(@resultsp,@i,1,NCHAR(UNICODE(substring(@OrigSpText1,@i,1))^(UNICODE(substring(@OrigSpText2,@i,1))^UNICODE(substring(@OrigSpText3,@i,1)))))SET@i=@i+1END--droporiginalSPEXECUTE('dropPROCEDURE'+@objectName)--removeencryption--preservecaseSET@resultsp=REPLACE((@resultsp),'WITHENCRYPTION',')SET@resultsp=REPLACE((@resultsp),'WithEncryption',')SET@resultsp=REPLACE((@resultsp),'withencryption',')IFCHARINDEX('WITHENCRYPTION',UPPER(@resultsp))>0SET@resultsp=REPLACE(UPPER(@resultsp),'WITHENCRYPTION',')--replaceStoredprocedurewithoutenryptionexecute(@resultsp)GO

ReaderFeedback

JoakimM.writes:Itriedthisscriptwithmixedresults.Itworksforsomeencryptedprocedures,butforothersIgeterrormeassageslike:

Server:Msg512,Level16,State1,Proceduresp_decrypt_sp,Line7.Subqueryreturnedmorethan1value.Thisisnotpermittedwhenthesubqueryfollows=,!=,<,<=,>,>=orwhenthesubqueryisusedasanexpression.

KarlCwrites:IgotthesamemessageasJoakimM.butuponfurtherinvestigationIfoundthatthishappensonlywhenstoredproceduresexceed4000characters.Whenthishappens,storestheprocedureacrossmultiplerowssoyougettheerror'subqueryreturnemorethan1row'.Togetaroundthatyoucanchangethestatement

SELECTctextFROMsyscommentsWHEREid=object_id(@objectNametoSELECTtop1ctextFROMsyscommentsWHEREid=object_id(@objectNameorderbycolid

Thatwillgetyouthefirstpartofthestoredprocedure,whichcan'tbecreatedbecauseitismissingtheendpartandisnotavalidsyntaxbutyoucanprint@resultspouttoseeit.1

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>