Decrypting Stored Procedures Thanksgiving Special:Quick Tips

Update: Thanks for your response Jon Gurgul! I have updated the files to reflect your updates. It's faster and cleaner now.

Happy Turkey day! Today we're going to eat, lounge around... and pray we're not called in. Something that I've found useful recently. You see a job calling a Stored Procedure that don't know what it does. You attempt to script it out and view it. Encrypted? Well... that's in the way. So do we now start a trace and run it hoping it doesn't cause duplicate data or truncate something you do't want it too? Eh, we can use this instead!

This is a script I found that was written by Jon Gurgul. The original article can be found here.

This is not my creation. All I did was build a step by step guide on how to do it in a more readable fashion.

The Word Document can be found here.

The .SQL file can be found here.

Something to note, the case sensitivity is an issue. I haven't gone through to figure out what's not cased the same... but this works for most databases. (Or... you can just temporarily change the collation... Be warned, I'd make sure no production data or any data is going into it at that time. I'd request a Scheduled Outage, that or trace the collation issue ^.^' )

This can be insanely useful if you're taking over an organization that everything was encrypted and you can't get into it, until now.

I'm adding the links where I found and built this information from below.


  1. I have posted an updated rewrite version on my site, with a few bugs removed. Thanks Jon Gurgul

    1. Thank you very much! I know we found this useful taking over a SQL box from under someone's desk. I appreciate the update and all the hard work.

  2. Alternatively download a trial copy of Red-Gate SQL Compare and compare the DB containing encrypted objects with an empty DB.

    It still astounds me that MS still allow the WITH ENCRYPTION option as it's so easy to work around.


    1. If you have a lot to go through, that may be a faster option. If you only have a few or if you're under a tight budget... This works free with no trial. Both are great options. ^.^


All opinions welcome! Please comment with any changes thoughts or discussion points.