Stored Procedure with Encryption in SQL Server 2008

Question

Hi
Ques Create the Stored Procedure with Encryption in SQL Server 2008 ?

Senthilkumar · Answer

Hi,
The stored procedure, view and functions can be encrypted using the 'WITH encryption' statement.
The schema will be encrypted and it can't be viewed the stored procedure. It will throw the message when try to view the schema.
The further stored procedure changes can be notified from the developers back in the local system.  The stored procedure can be encrypted. When you have complex business logics and want to hide the logic implementation then the schema of the stored procedure can be encrypted.
For example,
CREATE PROCEDURE Proc_RetrieveProducts WITH ENCRYPTION AS BEGIN         SET NOCOUNT ON                  SELECT ProductID, ProductName, Manfacturer         FROM Products
END
usually the stored procedure can be viewed by
1) SELECT * FROM sys.comments 2) SP_HELPTEXT 3) Sql server object explorer >> Under database >> stored procedure
It will show the message with the stored procedure encrypted.
I have written a article in the c-sharpcorner and you can view this by clicking
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/encrypt-the-stored-procedure-in-sql-server/

Satyapriya Nayak · Answer

Hi Arun,
Please refer the below links
http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/
http://beecy.net/post/2009/01/22/encrypt-sql-server-stored-procedure.aspx
Thanks