Decrypt encrypted 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

    Requires Free Membership to View

for SP's only and the other one for SP's, triggers and views. For version 1, the input is object name (stored procedure, view or trigger), and for version 2, 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.


For More Information

More on SQL Server basics

Learn what to do when upgrading SQL Server

Make a decision about whether taking advantage of SQL Server scalability is a good option

Read about how to configure and implement a SQL Server contained database

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Best Web Links: SQL Server tips, tutorials, scripts, and more.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Ask the Experts: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.


This was first published in July 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.