CREATE STORED PROCEDURE And UDF WITH ENCRYPTION

You can create encrypted stored procedures, so that no one can see the source code of the stored procedure. Even if you will try to see its text using sp_helptext procedure; it will show the following message

The text for object '[sptst_Test]' is encrypted.

Syntax:
Just add WITH ENCRYPTION Clause after the parameter's list of the stored procedure or UDF.

Example:

CREATE PROCEDURE [dbo].[sptst_Test]
-- Add the parameters for the stored procedure here
WITH ENCRYPTION
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * FROM sys.tables

END

NOTE:

• You can not decrypt the Encrypted stored procedure or UDF. So it is good practice not to use this security. Rather use the user permissions if you need to restrict some one.
• CLR UDF and Stored Procedures can not be Encrypted.