Recently a friend and I were talking about securing stored procedure code in a SQL server database.
From distant memory, I'm pretty certain that "with encryption" is incredibly easily broken in all versions of SQL Server, however he said it has been greatly improved in SQL 2005. As a result I have not seriously considered it as a security option in any systems I have ever worked on.
So in what scenarious could "with encryption" be used, and when should it be avoided at all costs?
It can be used to hide your code from casual observers, but as you say: it's easily circumvented.
It really can't be any other way, since the server needs to decrypt the code to execute it. It's DRM, basically, and fails for the same reason as all the other DRM does - you can't simultaneously hide the data, and allow it to be accessed.
Good response, the MSDN documentation on "WITH ENCRYPTION" seems to agree with your point, now calling it "obfuscated" rather then encrypted.
I've met a few developers who were completely unaware of this point however. Hopefully this question/response will inform others too.
Yes, it's easily broken. I had a situation this past week where I had to decrypt several sprocs that a former developer had encrypted for a client of mine. After decrypting it, which took a moderate effort, I wouldn't rely on that for any means of protecting intellectual property, passwords, user ids. Anything really.