Decrypt Stored Procedures 1

[roccorocks]

I am looking to Decrypt some Stored Procedures, any ideas? The reason for this is that the database I am looking through has hundreds of tables and hundreds of SP. I need to find out specifically what table(s) a particular SP is looking at (instead of spending hours going throught the PK and FK to match up each and narrow it down!).

Please let me know.

Rocco

 

[SonOfEmidec1100]

My understanding is this cannot be done - full stop.

 

[roccorocks]

Thanks for the quick reply. Oh well, looks like I am doing this the long way!

Rocco

 

[MDXer]

Not sure if you want to go this route but I tested it and was able to get the table name

Run Sql Profiler
Add SCANS and Stored Procedures to The Profile Events and Object ID To the Data Columns.

Run the Stored procedure. In profile you will see
SP:Starting 987654 Exec @RC = [dev].[dbo].[usp_TestEncrypt]
SP:StmntStarting 987654 --test encrypted text
Scan:Started 123456
Scan:Stopped 123456

Then run

select * from sysobjects where [id] = '123456'
in QA and it will tell you the name of the object scanned.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[osjohnm]

Hi

You can DECRYPT stored procedures. Create the following procedure and then execute it by passing in the ENCRYPTED procedure's name.


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

Hope this helps
John

 

[SonOfEmidec1100]

Thanks you have ruined my faith in encryption and the rest of the day.

Im amazed, somewhat defeats the purpose.

 

[Luvsql]

I also have some encrypted sps that I need to have a look at, but when I exec the above sp with @objectName='spname', I receive an error:

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.

Then magically, the encrypted sp is deleted from my database!! It's gone and have to restore.

 

[roccorocks]

Luvsql, I had the same exact problem. I think the SP only works with simple stored procs (no dynamic or complex cursors). I might be wrong, but I could not get it to work on some of the test SPs that I created to encrypt and decrypt!

Rocco

 

[ClayG]

You could look in the SysDepends table for tables referenced by the SP. SysDepends is not always 100% correct, but usually OK for this.

 

[zhavic]

Try to look at this thread thread183-1104655

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.