How to view an encrypted view or stored procedure

I have a fairly detailed description about this problem here.

In short, the object is not really encrypted, but rather obfuscated. Therefore we can retrieve the original back. The method is a little involved but it consists of these steps:

1. Connect to the instance using the Dedicated Admin Connection

2. Select the obfuscated code like this:

   SELECT @secret = imageval
   FROM   sys.sysobjvalues
   WHERE  objid = OBJECT_ID(@object_name);
   

3. Replace the object with another one that has the same name same object_id and the same length in bytes (e.g. using ALTER PROCEDURE)

4. Get the newly obfuscated code the same way as above
5. XOR the three values together (obfuscated original, replacement and obfuscated replacement)

That will give you the original code. However, as Kin mentioned, there might be support and even legal implications with doing this so be sure to consult your lawyer first.

The module text is encrypted using an RC4 stream cipher.

The RC4 initialization key is computed from the SHA-1 hash of:

• The database family GUID (from sys.database_recovery_status)
  Converted from uniqueidentifier to binary(16)
• The module's object ID (from the catalog views)
  Converted from integer to little-endian binary(4)
• The module's object sub-ID
  Converted from smallint to little-endian binary(2).

The module's object sub-ID is:

• 1 for an unnumbered stored procedure; or
• The procedure number for a (deprecated) numbered stored procedure; or
• 0 otherwise.

A suitably privileged user can then decrypt the module by:

1. Obtaining the encrypted binary from sys.sysobjvalues (using the DAC)
2. Computing the RC4 key as detailed above
3. Running the well-known standard RC4 algorithm on the binary
4. Converting the result from binary to nvarchar(max)

More details and a full code implementation in my article:

The Internals of WITH ENCRYPTION

Another third party tool you could use to decrypt encryted objects on the fly is Red Gate's SQL Prompt: http://www.red-gate.com/products/sql-development/sql-prompt/features

Hovering over the stored procedure will then allow you to see the decrypted creation script.

Disclaimers: this tool is commercial (with a 14 day free trial) and I work for Red Gate.