How can we view the body of an encrypted stored procedure in SSMS?

Question

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

What solution?

Answer 1

When a stored procedure is created using the ENCRYPTED option, SQL Server internally saves the text with the object definition in obfuscated format

The actual definition of the object is stored in the sys.sysobjvalues ​​system table, which is not directly accessible. When connecting to SQL Server using a dedicated administrator connection (DAC), you can select the imageval column, which stores the information.

If you do not allow your company or your client to use third-party tools, see this message on how to decrypt an 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 third-party tools.

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

In ApexSQL, Encrypted objects are treated like any other SQL Server object with the addition of their DDL script, even if it is encrypted using the Decrypt encrypted objects option

The script of the encrypted object is displayed in the embedded object details dialog box:

Disclaimer: I work for ApexSQL as a support engineer

Answer 2

Keep the script around what created the saved process in the first place.

There is no documented means to extract the text of the procedure after it is created using this option. There are clues in CREATE PROCEDURE if you are desperate to recover text:

ENCRYPTION

Indicates that SQL Server converts the source code of the CREATE PROCEDURE to obfuscation format. The result of obfuscation is not directly visible in any of the catalog views in SQL Server. Users who do not have access to system tables or database files cannot receive text obfuscation. However, the text will be available to privileged users, who can either access the system tables through the DAC port or directly access the database files. In addition, users who can connect the debugger to the server process can receive the decrypted procedure from memory at run time.

That is, you have to connect using the DAC and request undocumented tables - there is no visual option in SSMS.

Answer 3

You can decrypt using the SQL Compare tool. You need to create one database to compare sql script. The result of returning the sql script is decrypted. Soft link: http://www.red-gate.com/products/sql-development/sql-compare/