Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
2.71/5 (3 votes)
See more:
Hi to all


One of my collegue was created that sp's now i want to manage all those things, i dont know how to decrypt the procedure's, we lost the copy of that procedure.

I am using the following code to decrypt my procedure, but it did'nt work to get the procedure it always give the null value.

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 , @ct nvarchar(max)

--get encrypted data
SET @OrigSpText1= (SELECT top 1 ctext FROM syscomments  WHERE id = object_id(@objectName) order by colid)
SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)
EXECUTE (@OrigSpText2)
print @OrigSpText1

SET @OrigSpText3=(SELECT top 1 ctext FROM syscomments  WHERE id = object_id(@objectName) order by colid)
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)))))
print @resultsp
 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
set @ct = (SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
print @ct
execute( @resultsp)
GO


Thanks to all
Posted
Comments
janaka4595 5-Jul-13 7:51am    
nice its work thanks

try this

SQL
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 , @ct nvarchar(max)

 --get encrypted data
 SET @OrigSpText1= (SELECT top 1 ctext FROM syscomments  WHERE id = object_id(@objectName) order by colid)
 SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)
  EXECUTE (@OrigSpText2)
 print @OrigSpText1

 SET @OrigSpText3=(SELECT top 1 ctext FROM syscomments  WHERE id = object_id(@objectName) order by colid)
 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)))))
 print @resultsp
  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
 set @ct = (SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
 print @ct
 execute( @resultsp)
 GO

after that
bass your object to this proc
 
Share this answer
 
v2
Probably a naive question but have you tried accessing it via SSMS?
 
Share this answer
 
Comments
Simon_Whale 20-Sep-10 12:19pm    
you can execute it, but can't view the SQL Statements used to construct it
as its not stored in "plain text" on the server you will need to look at 3rd party tools.

Google suggestions
 
Share this answer
 
Just download Optillect's SQL Decryptor freeware
A modern tool that decrypts T-SQL procedures, functions, triggers, and views on any SQL Server :cool:
 
Share this answer
 
Optillect's SQL Decryptor freeware has worked for me as well. It gives you a couple of options. You can see the code of the sp if you just wanted to see it or you can have the sp decrypted on the server. The app just needs to connect to the sql server.
 
Share this answer
 
you can execute it, but can't view the SQL Statements used to construct it
 
Share this answer
 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900