Login
Important alert: (current site time 7/15/2013 7:00:48 PM EDT)
 

winzip icon

DECRYPT SQL SERVER 2000 STORED PROCEDURES, VIEWS AND TRIGGERS (WITH EXAMPLES)

 Email
Submitted on: 7/9/2002 6:26:39 AM
By: Joseph Gama 
Level: Advanced
User Rating: By 31 Users
Compatibility: SQL Server 2000
Views: 337497
 
     This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption" There are 2 versions: one for SP's only and the other one for SP's, triggers and views version 1: INPUT: object name (stored procedure, view or trigger) version 2: INPUT: object name (stored procedure, view or trigger), object type('T'-trigger, 'P'-stored procedure or 'V'-view) Original idea: shoeboy Copyright © 1999-2002 SecurityFocus adapted by Joseph Gama Planet Source Code, my employer and myself are not responsible for the use of this code This code is provided as is and for educational purposes only Please test it and share your results

 
winzip iconDownload code

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com

 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.


Other 13 submission(s) by this author

 
Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:
Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 
Other User Comments
7/22/2002 7:31:40 PM: Raj

Really Good Site
(If this comment was disrespectful, please report it.)
 
7/30/2002 4:58:33 PM: JDOG

This is great, our company neede to be able to view some stored procedures made by a company developing new software, and we can now view this and develop other apps and reports based on this. Thanks!
(If this comment was disrespectful, please report it.)
 
7/31/2002 2:05:12 AM: Joseph Gama

You are welcome. If you have any questions please let me know and I will gladly reply.
(If this comment was disrespectful, please report it.)
 
8/5/2002 12:47:22 AM: Tony

IT seems to work on some stored procedures and on others it will say subquery returned more than one value on line 22.

Is this due to the sproc using nvarchar(4000) for all of the internal variables? If so, is it dying on larger stored procedures? And if that is the case, how can this be overcome? I know using ntext is not an option since some of the functions you use won't work against an ntext variable.

This is a great piece of code. THanks for sharing.
(If this comment was disrespectful, please report it.)
 
8/5/2002 8:56:49 PM: Joseph Gama

You are welcome Tony. You are right, there is a size limit due to the data types we can use, nvarchar(4000) - 8000 bytes. This code is a proof of concept, I wanted to prove an idea from ShoeBoy (a genial cracker/hacker that I recommend everybody to search online for his security related articles). Using TSQL makes more impact on proving this security issue because it is inherent to SQL Server and everyone who works with SQL Server can understand it. I am working on it and it should be done in one month or less.
(If this comment was disrespectful, please report it.)
 
10/15/2002 4:25:18 PM: 

The script worked fine in a simple sp that i made myself to test it but doesn't work in sps that alrady existed. In fact the error returned is
(If this comment was disrespectful, please report it.)
 
10/15/2002 4:32:07 PM: 

...the error returned is << Subquery returned more than 1 value. This is not permitted when the subquery follows, =, !=, <, <=, >, >=, or when the subquery is used as an expression. >> Any suggestions can be very helpful!!!
(If this comment was disrespectful, please report it.)
 
10/15/2002 5:22:41 PM: Joseph Gama

Please email me the code for that SP. I think that maybe the SP has more than 8 kb which is the problem that I am still working on.
(If this comment was disrespectful, please report it.)
 
10/16/2002 8:44:09 AM: 

Well unfortunatetly these sps were made by another person and i can't see the code because of the encryption. I think the problem is the syntax: SELECT @variable = (SELECT ....) but i am not sure. So...waiting for your new version.
(If this comment was disrespectful, please report it.)
 
10/16/2002 12:12:55 PM: Joseph Gama

The select statement should fail for SP's over 8 kb because there will be more than one row returned.
You can checkout the size of those SP's to see if they are over 8 kb. Try this:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=566&lngWId= 5

(If this comment was disrespectful, please report it.)
 
11/4/2002 3:24:14 AM: 

Very Nice.!! ,Thank You
(If this comment was disrespectful, please report it.)
 
11/4/2002 3:47:54 AM: 

Hey 5G for this code, really helpfull... Thanks for sharing such code.
(If this comment was disrespectful, please report it.)
 
11/4/2002 3:28:17 PM: 

Joseph,

Time and time again, you have submitted some of the best code on PSC. I applaud your high quality work, and even more, your willingness to share it.

You're a rare bird!

Thank you,

- Ras
(If this comment was disrespectful, please report it.)
 
2/19/2003 1:14:12 AM: 

sir, i have tried this code but this code is not working properly , please gave me in detail.my quet. is How can i decrypt an encrypted stored procedure in sqlserver7. my email-id alhad_tamdu@india.com
wating for positive reply....
(If this comment was disrespectful, please report it.)
 
3/15/2003 11:31:09 PM: 

Do you know anything about decrypting passwords in a .sql file database if so PLEASE email me Gohan_Link@hotmail.com
(If this comment was disrespectful, please report it.)
 
3/20/2003 5:08:30 PM: 

Help! I ran the above proc and it deleted my original encrypted stored procedure! How do I get it back?
(If this comment was disrespectful, please report it.)
 
4/3/2003 6:31:43 PM: Joseph Gama

You have 3 solutions:
1-restore from a backup
2-restore from the log
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
3-use Lumigent's Log Explorer SQL Server Utility
(If this comment was disrespectful, please report it.)
 
4/16/2003 11:02:55 AM: 

Help! I can't find the second version that decrypts triggers and views.
(If this comment was disrespectful, please report it.)
 
5/16/2003 3:13:30 PM: Joseph Gama

dbo.DECRYPTSP2K.PRC this is the one that decrypts views and triggers

dbo.DECRYPT2K.PRC decrypts sp's only
(If this comment was disrespectful, please report it.)
 
6/1/2003 12:26:51 PM: 

Joseph Gama 's SQL decrypt code is quite useful; and I will earnestly add it to my tool bag - Daniel Adeniji
(If this comment was disrespectful, please report it.)
 
6/4/2003 12:16:40 AM: 

Hi Joseph,

Do you know a way to move around the 8k constraint?

Even after altering the code within the sp to:
SELECT ctext FROM syscomments where id = object_id(' + @objName +') AND colid = 1

then I change to colid = 2 etc...

but it keeps returning the first row?

Please advise.

Joseph
(If this comment was disrespectful, please report it.)
 
6/26/2003 5:45:27 AM: 

Hi,

Thanks for this - it's helped alot (i accidentally encrypted all the stored procedures in my database...!). If it's of any use I have a version of the Stored Procedure script that will decrypt larger procedures, but it's not too pretty!

(If this comment was disrespectful, please report it.)
 
7/1/2003 3:13:40 PM: 

How can I get this code?
(If this comment was disrespectful, please report it.)
 
7/4/2003 8:47:34 PM: 

...the error returned is << Subquery
returned more than 1 value. This is not
permitted when the subquery follows, =,
!=, <, <=, >, >=, or when the subquery
is used as an expression. >> Any
suggestions can be very helpful!!!

(If this comment was disrespectful, please report it.)
 
7/15/2003 5:47:34 AM: 

Decrypt2k works fine, but it's seems only on sp's encrypted via tSQL script. When I created sp via EM (or check encryption in design view) Decrypt2k completed successfully, but view was still encrypted.
(If this comment was disrespectful, please report it.)
 
7/21/2003 11:21:27 AM: 

Has someone got decrypt going on >4000 nchar stored proc. Am having problems with modifiying the original code to do it.
(If this comment was disrespectful, please report it.)
 
9/11/2003 11:47:29 PM: 

The link to the code is broken. Will some one repost?
(If this comment was disrespectful, please report it.)
 
9/12/2003 12:38:17 AM: Joseph Gama

The Digital Millennium Copyright Act (DMCA)...?
This code is also posted at www.sqlservercentral.com
(If this comment was disrespectful, please report it.)
 
11/5/2003 11:01:34 AM: 

how to decrypt function decrypted using "with encryption" ?????
(If this comment was disrespectful, please report it.)
 
11/5/2003 4:40:08 PM: 

Hi Joseph, thank you for this code. While this code is exactly what I need, I have a number of very large SPs that don't work. I see a number people have asked about how to modify this SP to successfully decrypt large SPs. Have any solutions been identified? Thank you for your help.
(If this comment was disrespectful, please report it.)
 
11/25/2003 3:14:03 PM: Zuriel

Joseph , congratulations for the script for decrypt sql server stores procedures.
I have this problem
---------------------------
"Server: Msg 512, Level 16, State 1, Procedure DECRYPTSP2K, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
---------------------------

Do you have the new version for fix that ??
Please send me your response to aaimaretti@zuriel.com.ar

Thanks!
(If this comment was disrespectful, please report it.)
 
12/3/2003 9:51:47 AM: 

I located a link that contains a modification of this code to account for the errors listed above and am including below, as I see many people ran into the same problem.

http://www.planet-source-code.com/URLSEO/vb/scripts/ShowCode!asp/txtCode Id!728/lngWid!5/anyname.htm
(If this comment was disrespectful, please report it.)
 
3/2/2004 2:25:50 AM: 

Hi Joseph, I recently discovered your code and find that the result retuns the stored procs still encoded, the procedures are existing ones and I don't have the source code, any suggestions?
(If this comment was disrespectful, please report it.)
 
4/28/2004 10:28:21 PM: 

I'd used this code to decrypt a sp, then i got this message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I found that in
(If this comment was disrespectful, please report it.)
 
6/11/2004 1:31:05 PM: 

The code to drop the original Stored Procedure without Error Catching need to be improve, since whenever there is an error, it is no recoverable for the original encrypted stored procedure.
Recommandation:
Put in Error checking machanism, especially for
(If this comment was disrespectful, please report it.)
 
6/11/2004 8:43:54 PM: 

thanx for the help. i also vote for your code. thank you
(If this comment was disrespectful, please report it.)
 
8/6/2004 7:17:26 AM: 

Hi,
Nice code but unfortunately hasnt worked for me yet.

The problem i face is that is says the procedure doesnt exist !

1. I search and prove that the stored procedure exists by doing a select
2. then i use your script EXEC DECRYPT2K @objectName

but i get the error:

Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 52
The object 'xxx' does not exist in database 'yyy'.
(If this comment was disrespectful, please report it.)
 
11/26/2004 9:05:57 PM: 

I have successfully used the decrypt code on stored procs, and triggers larger than one nvarchar(4000) could handle. I have also switched the code to print the output to the query window thus leaving the original code intact, this is done through the use of a transaction. My question is do you have a current contact to shoeboy or do you know how he came up with the XOR solution?
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:02:49 PM: 

The link to download the code is not working. I am wondring if anybody has this code. If yes, could you please send me the code as an attachment at co_2k3 at yahoo dot com.

Thanks.
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:25:19 PM: Joseph Gama

Hello,
Thank you for the positive feedback.
Please consider this:

If you need to decrypt >8k then do a serch on PSP for
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:26:35 PM: Joseph Gama

Before you try to decrypt you should backup the database. You can simply take it offline and make a copy of the mdf file.
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:27:02 PM: Joseph Gama

If you can't download the zip file then you should use the link for feedback at the bottom of this page and report the problem. I have no control over this website.
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:27:18 PM: Joseph Gama

I would recommend using Alexander Polozov's code:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=585&lngWId= 5
Or Jonathan Spinks' code:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=728&lngWId= 5
(If this comment was disrespectful, please report it.)
 
12/27/2004 1:28:32 PM: Joseph Gama

Thank you!
Joseph Gama
(If this comment was disrespectful, please report it.)
 
1/4/2005 8:17:11 AM: 

In my sql server 2000 database, i had four storedprocedure with encryption. I wanted to see the source of the encrypted stored procedure. so using the DECRYPT2K stored procedure I tried to decrypt those four encrypted stored procedure. By doing so i converted two sps. other two sps are deleted from the database. Now i want to recover the deleted stored procedure from the Database. Please help me out regarding this.
(If this comment was disrespectful, please report it.)
 
1/6/2005 5:50:25 PM: 

Can you please send me instructions on how to use this code???
(If this comment was disrespectful, please report it.)
 
1/6/2005 5:52:06 PM: 

Please let me know procedure to decrypt VIEWs and Stored Procedure and run this code on my SQL Server 2000 its says code command completed sucessfully but still I am not able to view any encrypted stored procedure or views...
(If this comment was disrespectful, please report it.)
 
3/11/2005 4:29:26 AM: 

Execllent work. Thanks for sharing.

I want one help you. That is I want to know that is it possible to encrypt all the SPs in one shot, if possible, can you pls share your knowledge.

Thanks in Advance.
Sundar

(If this comment was disrespectful, please report it.)
 
4/26/2005 7:10:14 AM: 

Thanks for sharing this,
Can you share ur knowledge on how to encrypt the stored procedure and share to everyone that procedure, they way u have shared

exec(decrypt(0x20D89BA2310 ...

Thanks in Advance
Tilok

(If this comment was disrespectful, please report it.)
 
5/30/2005 10:59:49 AM: 

Hi,
I have tried to decrypt an SP with DECRYPT2K(Object and Type), but this message return to me:
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
Can You Help me?
Best Regards
(If this comment was disrespectful, please report it.)
 
12/29/2005 1:20:33 AM: fishman

Ok,very good,thanks
(If this comment was disrespectful, please report it.)
 
2/3/2006 9:54:29 PM: CesarF

Hi, I've strong and opposite feelings.
First of all, YOU MADE MY DAY! thank you very much.
Second... I started to feel like the 'naked king' story.

(If this comment was disrespectful, please report it.)
 
10/9/2006 5:49:49 PM: Raj

Its really superb. thanks a lot
(If this comment was disrespectful, please report it.)
 
11/14/2006 7:06:28 AM: Albert

This code does not seem to take into account very long stored procedures etc. that may be split into multiple rows in syscomments. Thus it can potentially leave your system crippled if you didn't make a backup beforehand!
(If this comment was disrespectful, please report it.)
 
3/20/2007 10:40:09 AM: Geert

PROBLEM SOLVED:
When you get: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Then replace (4000-62) by (4000-63) and all goes well.
Good luck!
(If this comment was disrespectful, please report it.)
 
3/20/2007 11:13:35 AM: Geert

PROBLEM SOLVED: When you get: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Then replace (4000-62) by (4000-(37+len(@objName))) and all goes well.

FORGET MY PREVIOUS MESSAGE!!!
Good luck!

(If this comment was disrespectful, please report it.)
 
8/21/2007 5:35:25 AM: 

Thanks for your generous help! But...
It seem that replace (4000-62) by (4000-(37+len(@objName))) cannot solve the problems. I tried it with my encrypted stored!. The error message still displayed!
Wish you could fix it.
(If this comment was disrespectful, please report it.)
 
9/6/2007 2:01:24 PM: Rene

The problem which causes the 'Subquery returned more than 1 value' error message has nothing to do with the encryption. There are simply more than 1 rows in the syscomments table if the text exceeds the column's length! They have ascending [colid] values. In order to decrypt longer stored procedures you need to concat the texts stored in those rows.
(If this comment was disrespectful, please report it.)
 
5/13/2008 8:00:35 AM: Prakash Bangera

After executing DECRYPT2K my stored procedure got deleted from the database. Why is this happening??!!

Also I couldnlt recover it after it got deleted :(

Pls guide

(If this comment was disrespectful, please report it.)
 
11/19/2008 5:36:35 AM: Gary

Hi, I have made a modification to this code, so that it shows the SP instead of drop/recreate. This means if there is an error, you do not lose the stored procedure. Also i have modified the code to resolve the 'Subquery returned more than 1 value' error. I have uploaded this to http://www.garyweb.co.uk/decrypt_sp.sql

Cheers,

Gary.
(If this comment was disrespectful, please report it.)
 
9/21/2010 4:38:00 PM: Rydunzel

BE WARNED THIS CODE CAN DELETE YOUR STORED PROCEDURE !!!!!!!!!
(If this comment was disrespectful, please report it.)
 
10/12/2010 4:53:52 PM: Optillect Team

Just download Optillect's SQL Decryptor freeware at
http://optillect.com/products/sqldecryptor/overview.html
A modern tool that decrypts T-SQL procedures, functions, triggers, and views on any SQL Server.
(If this comment was disrespectful, please report it.)
 
Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.

Search:

Newest postings for SQL.

Add this ticker to my site

Daily code email

Click here to join the 'Code of the Day' mailing list!

This site on CD

Over 7,000 submissions on a super fast CD!

Code/Articles

Top Code

Community

Jobs

Other

Goto…

Copyright© 1997-2013 by Exhedra Solutions, Inc. All Rights Reserved.
By using this site you agree to its Terms and Conditions. Planet Source Code™ and the phrase "Dream It. Code It"™ are trademarks of Exhedra Solutions, Inc.