There was a query that I spent two weeks building and tuning. I had to figure out the business logic and do a lot of data discovery (SQL profiler). At then end I came out with some neat little code that was able to do somewhat of an ETL process between two of our Major HR DBs. This prevented the IT group from having to input data into two different systems. Anyways, due to our security policy, there were support staff that were poking around our SQL servers. They had figured out how to right-click in SSMS and see the body of a stored procedure. That is when I wanted to find out a way to prevent casual surfers from poking into code, and then coming back with questions.
The simple step I took, was to make stored procs of all our text queries, and then encrypt them. This is easy, and the code is not viewable from SSMS or by using sp_helptext. Throwing sp_password as a comment will further hide the code from being seen in the SQL profiler. Make sure you have a text copy of the proc stored safely. The code is as follows:
CREATE PROCEDURE proc_name
SELECT * from intellectul_table;