Decrypt Encrypted Stored Procedures

zhaozj2021-02-11  177

Http://searchDatabase.techtarget.com/tip/1,289483,ss13_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 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 planetsourcecode.com.

Create Procedure SP_Decrypt_sp (@Objectname Varchar (50))

AS

Declare @ Origsptext1 nvarchar (4000), @ igsptext2 nvarchar (4000), @ sptext3 nvarchar (4000), @Resultsp NVARCHAR (4000)

Declare @i int, @t bigint

- Get Encrypted Data

Set @ OrigspText1 = (Select ctext from syscomments where id = Object_id (@Objectname))

Set @ igsptext2 = 'alter procedure' @objectname 'with encryption as' replicate ('-', 3938)

Execute (@ ptext2)

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 ', (DatalesPtext1) / 2)))

--LOOP

While @i <= datalength (@ ptext1) / 2

Begin

--Reverse Encryption (xor Original Bogus Bogus Encrypted)

Set @Resultsp = stuff (@Resultsp, @i, 1, nchar (unicode (@ Origsptext1, @i, 1)) ^ (unicode (@ ptext2, @i, 1)) ^

Unicode (@ 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', '')

- Repece Stored Procedure without Enryption

Execute (@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

SubQuery Follows =,! =, <, <=,>>,> = or or the subs quiUERY 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 stores the procedure across multiple rows so you get the error 'subquery returne. More Than 1 Row '. To get arround this 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 colidThat will get you the first part of the stored procedure, which can not be created because it is missing the end part and is not a valid syntax but you can print @Resultsp Out to see it it.

For more information

Feedback: E-mail the editor with your thoughts about this tip More tips:. Hundreds of free SQL Server tips and scripts Tip 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 Best Web Links: SQL Server tips, tutorials, scripts, and more Forums:.. Ask your technical SQL Server questions - or help out your peers by answering them - in our active forums Ask the EXPERTS: OUR SQL, Database Design, Oracle, SQL Server, DB2, Metadata, and Data Warehousing Gurus Are Waiting to Answer Your Toughst Questions.

转载请注明原文地址:https://www.9cbs.com/read-6049.html

New Post(0)