How to decrypt stored procedure in SQL Server 2008

Question

I have a stored procedure which is encrypted using the WITH ENCRYPTION option. Now I want to decrypt that procedure. I have already tried a stored procedure called "Decryptsp2K" which is given for SQL 2000 in this forum: http://forums.asp.net/t/1516587.aspx/1

But it deletes my stored procedure, rather than decrypting it.

Is there a way to decrypt a stored procedure in SQL Server 2008?

Answer 1

The SQL Server Pro article "Decrypt SQL Server Objects" still works in SQL Server 2008.

You need to connect via the DAC. See the file "Decrypt SQL 2005 stored procedures, functions, triggers, views.sql" in the download.

Just to summarise the steps that it performs for the following stored procedure definition

CREATE PROC dbo.myproc
WITH ENCRYPTION
AS
SELECT 'FOO'

1. Retrieves the encrypted object text from the imageval column in sys.sysobjvalues and stores it in a variable @ContentOfEncryptedObject
2. Calculates @ObjectDataLength from DATALENGTH(@ContentOfEncryptedObject)/2.
3. Generates an ALTER PROCEDURE statement padded out to the correct length with the - character (so in this case ALTER PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS------------)
4. Executes the ALTER statement, retrieves the encrypted version from sys.sysobjvalues and stores that in the variable @ContentOfFakeEncryptedObject then rolls back the change.
5. Generates a CREATE PROCEDURE statement padded out to the correct length with the - character (so in this case CREATE PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS-----------). This gets stored in the variable @ContentOfFakeObject

It then loops through for @i = 1 to @ObjectDataLength and decrypts the definition a character at a time using the following XOR calculation.

NCHAR(
      UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
      (
          UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^
          UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
      )
     )

UPDATE

Paul White has written a very nice article that goes into details on why the above works, and that gives an alternate method that doesn't rely on altering the object: The Internals of WITH ENCRYPTION

Answer 2

If you want to decrypt procedure or any other encrypted object, check out ApexSQL Decrypt.

It’s a free standalone tool, with an option to integrate it into SSMS, preview original DDL script, and create Alter or Create decryption scripts.

From a standalone tool you can connect to multiple servers and decrypt multiple objects at once.

dbForge SQL Decryptor is the other tool that can help you out in this case as well.