How to decrypt stored procedure in SQL Server 2008
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.
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'
- Retrieves the encrypted object text from the
imageval
column insys.sysobjvalues
and stores it in a variable@ContentOfEncryptedObject
- Calculates
@ObjectDataLength
fromDATALENGTH(@ContentOfEncryptedObject)/2
. - Generates an
ALTER PROCEDURE
statement padded out to the correct length with the-
character (so in this caseALTER PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS------------
) - Executes the
ALTER
statement, retrieves the encrypted version fromsys.sysobjvalues
and stores that in the variable@ContentOfFakeEncryptedObject
then rolls back the change. - Generates a
CREATE PROCEDURE
statement padded out to the correct length with the-
character (so in this caseCREATE 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