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.