How do I protect my stored procedure code?

When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. There is a trivial way to do this in SQL Server, instead of: 
 

CREATE PROCEDURE dbo.foo  AS  BEGIN      SELECT 'foo'  END

 
You can use the WITH ENCRYPTION option: 
 

CREATE PROCEDURE dbo.foo  WITH ENCRYPTION  AS  BEGIN      SELECT 'foo'  END

 
Now, before you do this, make sure you keep the logic of the stored procedure in a safe place, since you won't have easy access to the procedure's code once you've saved it. 
 
Now you will notice that when you try to open the procedure in Enterprise Manager's GUI, you will receive the following error: 
 

Microsoft SQL-DMO  Error 20585: [SQL-DMO]  /******      Encrypted object is not transferable,      and script can not be generated.  ******/

 
And when you try to use sp_helptext to review the code: 
 

EXEC sp_helptext 'foo'

 
You will get the following error: 
 

The object comments have been encrypted.

 
Unfortunately, there are at least two ways to defeat this mechanism. One is to run SQL Profiler while executing the stored procedure; this often can reveal the text of the procedure itself, depending on what the stored procedure does (e.g. if it has GO batches, dynamic SQL etc). If they miss the initial install, the user can delete the stored procedures or drop the database, start a Profiler trace, and ask you to re-create them (in which case they will capture the CREATE PROCEDURE statements). You can prevent Profiler from revealing the text to snoopers by embedding sp_password in the code, as a comment: 
 

CREATE PROCEDURE dbo.foo  WITH ENCRYPTION  AS  BEGIN      SELECT 'foo'      -- comment: sp_password  END

 
Another way thatusers might be able to get at your encrypted code is to use readily available code that allows you to break SQL Server's relatively trivial encryption algorithm. You can find this code online if you know what you are looking for...