SQL Server Performance Forum – Threads Archive
how to decrypt a stored procedure?
Hi Could somebody please tell me how to decrypt a stored procedure? We have some stored procedures with encrption option so we can’t view them. How to re-create them? Thanks in advance!What kind of encryption s/w used?
If its a third party tool then contact Vendor itself. If SQL supplied tool is used then take help from thishttp://www.winnetmag.com/Article/ArticleID/14369/Windows_14369.html link. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
<pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE sp_decrypt_sp (@objectName varchar(50))<br />AS<br />DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)<br />declare @i int , @t bigint<br /><br />–get encrypted data<br /><br />SET @OrigSpText1=(SELECT top 1 ctext FROM syscomments WHERE id = object_id(@objectName) order by colid)<br />SET @OrigSpText2=’ALTER PROCEDURE ‘+ @objectName +’ WITH ENCRYPTION AS ‘+REPLICATE(‘-‘, 393<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />EXECUTE (@OrigSpText2)<br /><br />SET @OrigSpText3=(SELECT top 1 ctext FROM syscomments WHERE id = object_id(@objectName) order by colid)<br />SET @OrigSpText2=’CREATE PROCEDURE ‘+ @objectName +’ WITH ENCRYPTION AS ‘+REPLICATE(‘-‘, 4000-62)<br /><br />–start counter<br />SET @i=1<br />–fill temporary variable<br />SET @resultsp = replicate(N’A’, (datalength(@OrigSpText1) / 2))<br /><br />–loop<br />WHILE @i<=datalength(@OrigSpText1)/2<br />BEGIN<br />–reverse encryption (XOR original+bogus+bogus encrypted)<br />SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^<br /> (UNICODE(substring(@OrigSpText2, @i, 1)) ^<br /> UNICODE(substring(@OrigSpText3, @i, 1)))))<br /> SET @[email protected]+1<br />END<br />–drop original SP<br />–EXECUTE (‘drop PROCEDURE ‘+ @objectName)<br />–remove encryption<br />–preserve case<br />SET @resultsp=REPLACE((@resultsp),’WITH ENCRYPTION’, ”)<br />SET @resultsp=REPLACE((@resultsp),’With Encryption’, ”)<br />SET @resultsp=REPLACE((@resultsp),’with encryption’, ”)<br />IF CHARINDEX(‘WITH ENCRYPTION’,UPPER(@resultsp) )>0 <br /> SET @resultsp=REPLACE(UPPER(@resultsp),’WITH ENCRYPTION’, ”)<br />–replace Stored procedure without enryption<br />print ( @resultsp)<br /><br /></font id="code"></pre id="code"><br />this is bit similar to the satyas link. Bit in that satya’s link it alter the existing procedure. which I think not a godd idea. <br />in additon, I got into trouble when I used it innitially. <br />it drops the existing sp, but if there is a error when creating the sp, SP is no more. So I have modified it to display the sp.<br />
A simple search on Google should return many "useful" tools.
See if this helps:http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>