In essence, encryption of code within the engine is more like obfuscation than anything else. Unlike passwords and other sensitive data, the engine has to be able to read the code, so there has to be a way to reverse the so-called "encryption." While the engine inherently has the ability to do this internally, there is no direct way for us to do it ourselves out of the box (so if you play with the WITH ENCRYPTION option, be warned; you need to ensure you keep a copy of your module code in source control). Obviously decryption methods exist: some of which are free, and some you have to pay for – such as SQL Prompt Pro from Red-Gate:
I won't point you to the free ones, but will simply state that anyone with a search engine can find them.
Personally, I haven't used them, but I haven't been put in a situation where I felt I needed to. Others have. Last week on twitter there was a brief conversation on whether or not it was "okay" to look at a vendor's encrypted stored procedure; the reason: poor performance. With the assumption that the vendor was either non-responsive or did not have a good answer, I suggested that this would be okay in order to learn for yourself the potential cause of the performance problem – not necessarily so you can fix it yourself, and not necessarily to say to the vendor, "I told you so," but it might actually yield information about a problem in your own part of the system (e.g. a missing index or a poorly networked linked server).
One of the comments @Dave_Levy made was:
I guess this is one of those areas where people willing to cut corners can get ahead of the rest of us.
I wasn't sure exactly how this constituted "cutting corners." If the vendor is not owning up to the issue and taking responsibility for their code, and as long as you're not learning from the code in such a way to compete with them (or to replace their solution with your own), then personally I think it is within reason to use the tools at your disposal to get to the bottom of the issue.
Later in the discussion it turned out that this peeking would be a direct violation of the licensing agreement with the vendor (which is probably the case in most situations). The software vendors who sell products that use encrypted stored procedures know that it is not protection of their intellectual property, but rather a deterrent for the casual observer. So encryption is just a minor roadblock slowing down a determined user; the way they really enforce the protection of their IP is through their EULA.
So it is quite a fence to be sitting on : suffer the performance problem, or try to get to the bottom of it? wait for the vendor to fix it (or to refuse to), or arm yourself with enough information to argue with them? stand by your morals? switch vendors? If what you learn from inspecting the code is that the vendor should have no business putting code anywhere near your systems, then that's something, right?
I realize we all want to be loyal to our vendors, but if they aren't willing to work with us, our only other alternative seems to be to either violate the EULA or uninstall the software; both options have their pros and cons from both sides. I'm curious what others' opinions on this are?