Decrypting Stored Procedure

If you dont take any backup before  ENCRYPTION, then you need to use third-party tools for decrypting it.
For SQL Decryptor tool : Download dbForge SQL Decryptor (sqldecryptor.exe)

> Install dbForge SQL Decryptor (with default settings)
> Login to the server through server name, user name & password
> Go to the required SP. right on SP & select "Show DDL Script"

Wait for few second.... SP body will be shown in a window

*** If you need to decrypt a number of stored-procedures or any other encrypted objects, I recommend using the Decryption Wizard. To do this, 
>go to the Action menu(or right click on DB name) and select ‘Decryption Wizard…’. 
>Once the wizard has opened, you can select all the objects you want to decrypt at once and what to do with the output of the wizard. 
You can have the T-SQL output go into a single file, create one file per object, or decrypt all the objects in place. 

>After you have selected the options you want for your batch, click the Execute button.
>Check link for better understanding


Link: https://www.wearediagram.com/blog/using-sql-decryptor-to-work-with-encrypted-sql-server-objects
Download : https://www.devart.com/dbforge/sql/sqldecryptor/

Decrypting with ApexSql

Another third party free tool is "ApexSQL Decrypt"
Download Link: https://www.apexsql.com/Download.aspx
Tutorial link: https://www.apexsql.com/sql-tools-decrypt.aspx
I download it using :
gMail: [email protected]
mobile :01676057738
company: abc

Encrypting Function UDF

CREATE FUNCTION MTB_fnWithEncryption
(
    @value varchar(50)
) 
RETURNS varchar(50) WITH ENCRYPTION --- In function "WITH ENCRYPTION" occurs after "RETURNS"
AS
BEGIN

-- rest of function here
 
END
GO

Encrypting Stored Procedure

/*---- At first take a backup of SP. Coz after Encryption you will not able to modify 
the SP any more. 
When required run the sp script without this line : "-- with ENCRYPTION". 
Then "Modify" option will be enable again without using any 3rd party tool.*/

ALTER PROCEDURE [dbo].[HRMS_Company_T_Get_SP] 
 @CompId int = 0 
 
 with ENCRYPTION ----*** Add this line here for Encrypting SP ***

AS
BEGIN
	
			SELECT [CompId] , [CompCode] ,[CompGroupId],GrpCOMP.GrpCompNameEng, [CompNameEng] , [CompNameBan] , [CompRemarks] , 
			[CompIsActive] , [CompAddressEng] , [CompAddressBan] , [CompPhone] , [CompMobile] , [CompFax] , [CompEmail] , [CompWeb] ,
			[CompLogo], [CompCreator] , [CompCreationDate] , [CompModifier] , [CompModificationDate]
			FROM [HRMS_Company_T] Comp left outer join [HRMS_GroupCompany_T] GrpCOMP ON COMP.CompGroupId=GrpCOMP.GrpCompId
			WHERE ([CompId] =@CompId OR @CompId = 0) ORDER BY [CompNameEng]
	
END

Encrypting Views

CREATE VIEW TestView WITH ENCRYPTION --- In VIEW "WITH ENCRYPTION" occurs after VIEW name
AS
select * from Application.Countries
Go

Pros Cons

Pros & Cons of Using WITH ENCRYPTION: 
Pros
  1.  This is one among the easiest method to hide the code from plain sight.
  2.  You can hide low security information or propriety source code in the stored procedures views and user defined functions.

Cons
    The encrypted SQL objects could be easily decrypted. There are several tools available in the internet to decrypt. So it’s not advisable to depend on this technique to hide highly confidential information or code.