I recently had to view the code behind a couple stored procedures in SQL Server. Usually that’s a very simple thing to do. Just right click the stored procedure, click Modify OR script-as “Create New” and it spits the entire stored procedure in a query window. Unfortunately for me, the stored procedures were encrypted so the usual method mentioned above did not work.
There are a couple blogs online that mention logging in your SQL Server via the DAC and running some scripts to see the code, but that can get daunting and time-consuming..especially to a novice.
I came across a very neat piece of software called dbForge SQL Decrypter by Devart. It’s absolutely free and easy to setup. I tried it out and it works like a charm! Check it out below:
How To Decrypt Stored Procedure In SQL Server
After installing dbForge SQL Decrypter, double click the icon and the “Connect to Server” opens:
***Note: I expanded the “Show Advanced Settings” to show that you can choose to log into your server via the DAC (Dedicated Administrator Connection). It’s not required to choose it, but I wanted to show it to you anyway.
Once you log in, you’ll notice it resembles SQL Server Management Studio’s Object Explorer. Go ahead and navigate to the stored procedure: Databases –> MyDB –> Procedures. I created a dummy encrypted stored procedure called testProcWithEncryption for demo purposes.
When you right-click the encrypted stored procedure you get the following options:
When you click on “Show DDL script“, the software decrypts the stored procedure and spits the code in the right-pane window:
Done! Wasn’t that easy?
What Else Can dbForge SQL Decrypter Do?
It has more neat features which makes it absolutely convenient to use. Below are a couple features that make is stand even more:
Batch Decryption Wizard – If you have multiple stored procedures, functions etc, you can decrypt them all at once and save it to a new T-SQL script file.
Syntax Highlighting – People usually underestimate how annoying it is when there is no syntax highlighting. This feature makes it easy for readability and helps in easily finding what you’re looking for.
Saving Definition to a File – This is super convenient. Instead of cutting and pasting into another text file, etc.
The Best Feature of them all …
The best thing about Devart’s dbForge SQL Decrypter is that it’s absolutely free! I’ve already added it to my arsenal of must-have DBA tools. Feel free to download it yourself and try it out!
It will save TONS of time on trying to decrypt a Stored Procedure, Triggers, Views, or Function yourself.