How can we view the encrypted stored procedure's body in SSMS?

Question

I created a new stored procedure WITH ENCRYPTION statement, now I want to view its body.

What is the solution?

Answer 1

In case when stored procedure is created with the ENCRYPTED option, SQL Server internally stores the text with the definition of the object in an obfuscated format

The actual definition of an object is stored in system table sys.sysobjvalues which is not directly accessible. By connecting to SQL Server using the Dedicated Administrator Connection (DAC) you can select the imageval column in which the information is stored

If you are not allowed by your company or your client to use third party tools see this post on how to decrypt the encrypted object:

http://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

However, the easiest way is to use the third party tools

One of them is ApexSQL Complete, a FREE SSMS and VS add-in

In ApexSQL Complete encrypted objects are handled as any other SQL Server object with addition that their DDL script is shown, even if it is encrypted using the Decrypt encrypted objects option

The script of an encrypted object is shown in the inline object details dialog:

Disclaimer: I work for ApexSQL as a Support Engineer

Answer 2

Keep the script around that created the stored proc in the first place.

There's no documented means of retrieving the text of the procedure once it's been created with this option. There are hints in CREATE PROCEDURE, if you're desperate to recover the text:

ENCRYPTION

Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users who can either access system tables over the DAC port or directly access database files. Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

That is, you'll have to connect using DAC and query undocumented tables - there's certainly no visual option in SSMS.