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.

Decryption Script http://www.jongurgul.com/blog/sql-object-decryption/ - Jon Gurgul

Access to DAC: http://msdn.microsoft.com/en-us/library/ms189595.aspx -Microsoft

Expansion on Decryption http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330 - MSDN

Expansion on DAC http://beyondrelational.com/modules/2/blogs/77/posts/11314/dedicated-administrator-connection-underappreciated-features-of-microsoft-sql-server.aspx - Nakul Vachhrajani