Decrypt Encrypted Stored Procedure using SQL Server Tool
To decrypt an encrypted stored procedure, trigger, SQL view or user defined function on SQL Server database is not possible without using third-party tools like dbForge SQL Decryptor.
I recently required to decrypt encrypted SQL stored procedure codes. Since the stored procedure was not touched for a long time and running on a database for a small application, the original SQL source codes of the stored procedure was not found.
The procedure was connecting to another SQL Server database server with specific credentials and I have to decrypt stored procedure for SQL authorization data.
sp_helptext Not a Tool to Decrypt SQL Objects
When a SQL Server object like a stored procedure is encrypted, sp_helptext stored procedure does not show the SQL codes of the encrypted object.
You can create encrypted stored procedure as a sample on your development database and try to display text of that SQL stored procedure.
Let's try running sp_helptext to show source of our sample encrypted stored procedure.
execute sp_helptext 'kodyaz.EncryptedStoredProcedure'
Unfortunately what ever the SQL Server object type like a SQL stored procedure, trigger, SQL view or a user-defined SQL function, the outcome of the sp_helptext will not change:
The text for object 'kodyaz.EncryptedStoredProcedure' is encrypted.
So using sp_helptext is not a solution for displaying text of the encrypted SQL Server objects like an encrypted stored procedure, view or function on your database.
How to Decrypt Stored Procedure Code on SQL Server
What is the tool for database administrator or a SQL developer to decrypt encrypted database object in SQL Server?
Unfortunately SQL Server data platform does not provide a handy tool for database professionals to decrypt encrypted stored procedures, triggers, views or functions.
We have to check third-party database tool vendors like Devart for a solution.
I recently used Devart's dbForge SQL Decryptor to decrypt encrypted stored procedures in my databases.
dbForge SQL Decryptor is a free tool for decryption of database objects.
I appreciate dbForge SQL Decryptor with its easy to use GUI and effective functionality to decrypt stored procedures which are encrypted on SQL Server.
This SQL tutorial will continue with basic steps to decrypt an encrypted SQL stored procedure using free dbForge SQL Decryptor tool.
You can download tool for free from its vendor site: download
After you complete installation which will last only for a few minutes, you can now connect to SQL Server instance where the encrypted database objects exist for decryption.
Drill-down until you reach the target database object in the Object Explorer windows as in below screenshot from the SQL Decryptor tool.
Then right click on the stored procedure and choose Show DDL script menu option from the context menu. Or simply double-click on the encrypted stored procedure.
That is it! SQL Decryptor will display the code source of the stored procedure, trigger, user function, view or what ever the encrypted object is.
Another option that SQL Decryptor provides is Decrypt in-place option. If you use "Decrypt in-place", this tool will convert encrypted stored procedure to a normal database stored procedure.
So it decrypts the database object and then by executing ALTER command without "With Encryption" converts the stored procedure, etc. to a normal decrypted stored procedure.