Decryptencryptedstoredprocedures

核心提示：http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.htmlThis SP will decrypt Stored Procedures, Views or Triggers that were encrypted using &qu

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

---

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption". It is adapted from a script by asp?txtCodeId=505&lngWId=5">Joseph Gama and ShoeBoy. There are two versions: one for SP's only and the other one for SP's, triggers and views. For version 1, the input is object name (stored procedure, view or trigger), and for version 2, the input is object name (stored procedure, view or trigger), object type ('T'-trigger, 'P'-stored procedure or 'V'-view). From asp?txtCodeId=505&lngWId=5">PlanetSourceCode.com.

create  PROCEDURE sp_decrypt_sp (@objectName varchar(50))ASDECLARE  @OrigSpText1 nvarchar(4000),  @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)declare  @i int , @t bigint--get encrypted dataSET @OrigSpText1=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)EXECUTE (@OrigSpText2)SET @OrigSpText3=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)--start counterSET @i=1--fill temporary variableSET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))--loopWHILE @i<=datalength(@OrigSpText1)/2BEGIN--reverse encryption (XOR original+bogus+bogus encrypted)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--drop original SPEXECUTE ('drop PROCEDURE '+ @objectName)--remove encryption--preserve caseSET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', ')SET @resultsp=REPLACE((@resultsp),'With Encryption', ')SET @resultsp=REPLACE((@resultsp),'with encryption', ')IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0   SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', ')--replace Stored procedure without enryptionexecute( @resultsp)GO

Reader Feedback

Joakim M. writes: I tried this script with mixed results. It works for some encrypted procedures, but for others I get error meassages like:

Server: Msg 512, Level 16, State 1, Procedure sp_decrypt_sp, Line 7.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Karl C writes: I got the same message as Joakim M. but upon further investigation I found that this happens only when stored procedures exceed 4000 characters. When this happens, sql server(WINDOWS平台上强大的数据库平台) stores the procedure across multiple rows so you get the error 'subquery returne more than 1 row'. To get around that you can change the statement

SELECT ctext FROM syscomments WHERE id = object_id(@objectNameto SELECT top 1 ctext FROM syscomments WHERE id = object_id(@objectName order by colid

That will get you the first part of the stored procedure, which can't be created because it is missing the end part and is not a valid syntax but you can print @resultsp out to see it.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free sql server(WINDOWS平台上强大的数据库平台) tips and scripts.
• Tip contest: Have a sql server(WINDOWS平台上强大的数据库平台) tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Best Web Links: sql server(WINDOWS平台上强大的数据库平台) tips, tutorials, scripts, and more.
• Forums: Ask your technical sql server(WINDOWS平台上强大的数据库平台) questions--or help out your peers by answering them--in our active forums.
• Ask the Experts: Our SQL, Database Design, Oracle(大型网站数据库平台), sql server(WINDOWS平台上强大的数据库平台), DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.