Problem solve Get help with specific problems with your technologies, process and projects.

How to decrypt encrypted stored procedures

Here is a stored procedure used to decrypt compiled T-SQL stored procedures.

This stored procedure 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 stored procedures only and the other one for stored procedures, triggers and views. For version one, the input is object name (stored procedure, view or trigger), and for version two, 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),  @OrigSpText2 nvarchar(4000) , @OrigSpText3 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 @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))

--loop
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 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 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.


Dig Deeper on Win Development Resources

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close