SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.


Leave a comment on the original post [www.dbaonthego.com, opens in a new window]

Loading comments...