After having a quick look around the internet on the merits of the added security;
In SQL server 2000, the de-obfuscated text is stored in the SYSCOMMENTS table before it is executed. So you may wish to consider this as a preventative measure for the casual nosey parker, but not a bullet proof solution. However the equivalent MSDN page for SQL 2008 R2 doesn't mention this.