MSSQL网页编程之Decrypt encrypted stored procedures

虽然可以将一个droptable语句转换成先delete再删表，性能却会降低很多。这里我们用上面说道的另外一种可用数据：“操作前数据备份”。
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.html



ThisSPwilldecryptStoredProcedures,ViewsorTriggersthatwereencryptedusing"withencryption".ItisadaptedfromascriptbyJosephGamaandShoeBoy.Therearetwoversions:oneforSPsonlyandtheotheroneforSPs,triggersandviews.Forversion1,theinputisobjectname(storedprocedure,viewortrigger),andforversion2,theinputisobjectname(storedprocedure,viewortrigger),objecttype(T-trigger,P-storedprocedureorV-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(NA,(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,SQLServerstorestheprocedureacrossmultiplerowssoyougettheerrorsubqueryreturnemorethan1row.Togetaroundthatyoucanchangethestatement

SELECTctextFROMsyscommentsWHEREid=object_id(@objectNametoSELECTtop1ctextFROMsyscommentsWHEREid=object_id(@objectNameorderbycolid

Thatwillgetyouthefirstpartofthestoredprocedure,whichcantbecreatedbecauseitismissingtheendpartandisnotavalidsyntaxbutyoucanprint@resultspouttoseeit.

ForMoreInformation

Feedback:E-mailtheeditorwithyourthoughtsaboutthistip.Moretips:HundredsoffreeSQLServertipsandscripts.Tipcontest:HaveaSQLServertiptoofferyourfellowDBAsanddevelopers?Thebesttipssubmittedwillreceiveacoolprize--submityourtiptoday!BestWebLinks:SQLServertips,tutorials,scripts,andmore.Forums:AskyourtechnicalSQLServerquestions--orhelpoutyourpeersbyansweringthem--inouractiveforums.AsktheExperts:OurSQL,DatabaseDesign,Oracle,SQLServer,DB2,metadata,anddatawarehousinggurusarewaitingtoansweryourtoughestquestions.对于insert操作，只需要把event_type改成DELETE_ROWS_EVENT；对于delete操作，改成WRITE_ROWS_EVENT

总感觉自己还是不会SQL

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

是要和操作系统进行Socket通讯的场景。否则建议慎重！

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

可以动态传入参数，省却了动态SQL的拼写。