SQL Server offers a way to encrypt your Stored Procedures to make sure that prying eyes can not see what is going on behind the scenes. The problem with this method is that it is not a very secure way of encrypting the contents of your stored procedures. In addition, since SQL Server basically stores your source code vs. a compiled version most people rely on the code that is in the database server instead of moving the code to a source control application. Because of the need to access this code, this tip outlines various methods of decrypting your encrypted database objects.
Basically, it is a simple option that you use when creating your objects. Here is a basic example using the "WITH ENCRYPTION" option:
CREATE PROCEDURE uspGetAuthors WITH ENCRYPTION AS SELECT * FROM authors GO
After you create this stored procedure, when you try to view the contents of the stored procedure
using the following command,
You will get this error: "The object comments have been encrypted."
Also, when trying to look at this stored procedure in Enterprise Manager You will get the following error message.