SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

sp_helptext fails on encrypted SQL stored procedure

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.

dbForge SQL Decryptor free SQL Server tool

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.

free SQL Server tool for decryption of database objects

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.

decrypt SQL Server stored procedure

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.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems