This article is written to answer following two questions I have received in last one week.
Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?
Answers 1) Use WITH ENCRYPTION while creating Stored Procedure or User Defined Function. 2) Sorry, unfortunately there is no simple way to decrypt the code. Hard way is too hard to even attempt.
Explanations of WITH ENCRYPTION clause If SP or UDF are created WITH ENCRYPTION it is one way street and it is not possible to decrypt it using SQL Server commands. It is always advised to save a copy of the script used to create the SP or UDF on other media than SQL Server. There is no way to get the original source code once it is executed on Server. Only privileged users who can access system tables over the DAC port or directly access database files as well as can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.
CLR SP and UDF can not be encrypted. SQL Server replication can not replicate encrypted SP or UDF.
I believe in fair programming techniques and strongly recommend against encryption. There should be no need to encrypt any code. If there is need to hide any code from certain users in that case user should be restricted using user login permissions.
Reference : Pinal Dave (https://blog.sqlauthority.com)
