SET NOCOUNT ON

DECLARE @owner sysname='dbo', @name sysname='sp_someprocedure';

-----------------------------------------------------------

--- Declarations:

DECLARE @offset int=1;

DECLARE @datalength int;

DECLARE @encrypted_object nvarchar(max);

DECLARE @decrypted_object nvarchar(max)=N'';

DECLARE @fake_object nvarchar(max);

DECLARE @fake_encrypted_object nvarchar(max);

DECLARE @lf nvarchar(max)=NCHAR(13)+NCHAR(10);

DECLARE @type varchar(128);

DECLARE @object_id int=OBJECT_ID('['+@owner+'].['+@name+']');

DECLARE @a int, @b int, @c int;

--- Check that the object exists

IF (@object_id IS NULL) BEGIN;

RAISERROR('Object does not exist.', 16, 1);

RETURN;

END;

--- Check that the object really is encrypted.

IF (NOT EXISTS (SELECT TOP 1 * FROM sys.sql_modules

WHERE [object_id]=@object_id AND [definition] IS NULL)) BEGIN;

RAISERROR('Object is not encrypted.', 16, 1);

RETURN;

END;

--- Store the SQL type name of the object in @type

SELECT @type=(CASE [type]

WHEN 'P' THEN 'PROCEDURE'

WHEN 'TR' THEN 'TRIGGER'

WHEN 'V' THEN 'VIEW'

ELSE 'FUNCTION' END)

FROM sys.objects

WHERE [object_id]=@object_id;

--- @encrypted_object is the encrypted, binary, version of the code:

SELECT TOP 1 @encrypted_object=imageval

FROM sys.sysobjvalues

WHERE [objid]=@object_id AND valclass=1 and subobjid=1;

SET @datalength=DATALENGTH(@encrypted_object)/2;

--- We're going to ALTER the existing object to a "known plaintext"

--- with encryption. That way, we can reverse-engineer the encryption

--- key, using the new encrypted object.

--- All of this is done in a transaction that we'll roll back when

--- we're done with it.

SET @fake_object=N'ALTER '+@type+N' ['+@owner+N'].['+@name+N']

WITH ENCRYPTION AS

';

--- Fill the fake object with dashes ("-") until it's as long as

--- the encrypted object.

WHILE (DATALENGTH(@fake_object)/2<@datalength) BEGIN;

IF (DATALENGTH(@fake_object)/2+4000<@datalength)

SET @fake_object=@fake_object+REPLICATE(N'-', 4000);

ELSE

SET @fake_object=@fake_object+REPLICATE(N'-',

@datalength-(DATALENGTH(@fake_object)/2));

END;

BEGIN TRANSACTION;

--- Implement the fake encrypted object:

EXEC(@fake_object);

--- Retrieve the encrypted version of the "known plaintext".

SELECT TOP 1 @fake_encrypted_object=imageval

FROM sys.sysobjvalues

WHERE [objid]=@object_id AND valclass=1 and subobjid=1;

--- Now that we have the encrypted fake object, roll back

--- the transaction, so we don't break the original object.

ROLLBACK TRANSACTION;

--- Change the @fake_object from ALTER to CREATE (because this is

--- how the encrypted objects are stored in the database!)

SET @fake_object='CREATE'+SUBSTRING(@fake_object, 6, LEN(@fake_object));

-----------------------------------------------------------

--- Perform decryption using the three versions: the encrypted

--- code, the plaintext fake code and the encrypted fake code.

WHILE (@offset<=@datalength) BEGIN;

SELECT

@a=UNICODE(SUBSTRING(@encrypted_object, @offset, 1)),

@b=UNICODE(SUBSTRING(@fake_object, @offset, 1)),

@c=UNICODE(SUBSTRING(@fake_encrypted_object, @offset, 1));

SELECT @decrypted_object=@decrypted_object+NCHAR(@a^(@b^@c)),

@offset=@offset+1;

END;

-----------------------------------------------------------

--- Print the results:

WHILE (@decrypted_object IS NOT NULL) BEGIN;

PRINT LEFT(@decrypted_object,

CHARINDEX(@lf, @decrypted_object+@lf)-1);

SET @decrypted_object=NULLIF(

SUBSTRING(@decrypted_object,

CHARINDEX(@lf, @decrypted_object+@lf)+LEN(@lf),

LEN(@decrypted_object)), '');

END;