I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.
In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.
This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.
Decrypting a Batch
The setup is in the previous post, and its the same here. Once that is complete, connect to SQL Compare and set up a project with the databases that have the encrypted procedures as the source. The destination doesn’t matter. You could pick any empty database.
Once that’s done, run the comparison. You ought to get results like this. Notice on this SQL 2017 instance, both procs are decrypted.
These do contain the WITH ENCRYPTION text, as this is an exact decryption. If I deploy this to another database, the procedures will be decrypted there.
Moving Procs Without Encryption
For many of us, if we were copying this procs, we want them decrypted. Fortunately, SQL Compare makes this easy. There are project options for this. Notice below I’ve searched for options related to encryption. I selected the “Decrypt encrypted objects” to allow SQL Compare to show the code above.
When I check the “Ignore WITH ENCRYPTION”, this will give me the code without that option. I check this and refresh my project and notice the code below. No WITH ENCRYPTION added.
I can now decrypt a lot of procedures in a batch, and do this as needed since if some are encrypted by a developer, likely you’ll find lots of random places as that developer tried to protect code in every database they touched./
I tested this on SQL Server 2017/2019/2022 and it worked. SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an eval today.