sql server - 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