ITPub博客

首页 > 应用开发 > IT综合 > Decrypt encrypted stored procedures (转)

Decrypt encrypted stored procedures (转)

原创 IT综合 作者:gugu99 时间:2007-10-19 17:40:47 0 删除 编辑
Decrypt encrypted stored procedures (转)[@more@]


This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption". It is adapted from a script by --code.com/vb/scripts/ShowCode.?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 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 .

create PROCEDURE sp_decrypt_sp (@objectName varchar(50)) AS DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000) declare @i int , @t bigint --get encrypted data SET @OrigSpText1=( ctext FROM symments 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 counter SET @i=1 --fill temporary variable SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2)) --l WHILE @i<=datalength(@OrigSpText1)/2 BEGIN --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+1 END --drop original SP EXECUTE ('drop PROCEDURE '+ @objectName) --remove encryption --preserve case SET @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 enryption execute( @resultsp) GO


Reader Feeack

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 petted 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, SERVER 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(@objectName to 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

  • to:tdichiara@techtarget.com">Feedback: the editor with your thoughts about this tip.
  • Index/0,289482,sid13_tax286331,00.html">More tips: Hundreds of free tips and scripts.
  • contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Links/0,289521,sid13_tax282894,00.html">Best Web Links: SQL Server tips, tutorials, scripts, and more.
  • : Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • : Our SQL, Database Design, , SQL Server, , metadata, and data warehousing gurus are waiting to answer your toughest questions.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10748419/viewspace-977618/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    3121
  • 访问量
    2354228