Looking around for how to encrypt stored procedure in Sql Server 2015, you are at the right place. At times, we need to encrypt the text of stored procedures as the SPs might contain sensitive or confidential data/information. So Sql Server provides WITH ENCRYPTION to encrypt the Stored Procedure.
CREATE procedure [dbo].[ImplementEncryption]
WITH ENCRYPTION AS
SELECT top 1 name, designation
Order by employeeid
Once we encrypt stored procedure by using WITH ENCRYPTION, we wont be able to view the stored procedure . Any attempt to view the stored procedure returns a message specifying that the text in stored procedure is encrypted:
EXEC sp_helptext ImplementEncryption
The text for object ‘ImplementEncryption’ is encrypted.
One important point to note is: Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text.
SQL professionals agree that the default stored procedure encryption that comes with MSSQL 2005, 2000 and 7.0 is ineffective and easily broken. In fact, Google generates over 3700 links for a search phrase “SQL decryptor”, offering dozens of software solutions that can bypass MSSQL “native” encryption. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.
One other way to see the text in encrypted stored procedure is to attach a debugger to the server process. Then we can retrieve the decrypted procedure from memory at runtime.