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
I have posted an updated rewrite version on my site, with a few bugs removed. Thanks Jon GurgulReplyDelete
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.Delete
Alternatively download a trial copy of Red-Gate SQL Compare and compare the DB containing encrypted objects with an empty DB.ReplyDelete
It still astounds me that MS still allow the WITH ENCRYPTION option as it's so easy to work around.
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. ^.^Delete