Encrypt stored proceudres with password.

Question

Hi All,
Is there a way to encrypt stored procedure code with password ?. I am not talking about the keyword "WITH ENCRYPTION" in the stored procedure when creating it but actually applying a password to it so that I can also decrypt it with that password. Don't want to create a master key and certificate for it either. Is there a way ? This is for any SQL Server versions from 2012 to 2019.

Thanks.

Answer 1

No. Think of it. To be able to run the stored procedure, SQL Server needs to be able read the SQL code. So if there were such a feature, user would only be able to run the procedure if the user has the password.

Answer 2

Hi @Carlos D ,

No. It is not possible. Refer to How to Encrypt a Stored Procedure in SQL Server.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you all. I knew it but just wanted to verify. The reason for this is because we are managing other companies database servers and implementing our own stored procedures. When the company ends the contract, we need to remove these stored procedures and not have them use them anymore.

Answer 4

For what you describe, I create a "license check" encrypted proc. That proc just returns 0 or 1 if the date is <= a date like 1/1/2021. Then each encrypted proc, calls this proc first before running and reports "License violation" after the date. If you need to extend the procs past that date, you just replace the encrypted proc and a new date.

There is not really a good way to stop someone from using code on their server.

Answer 5

The point is that the SPs will be used as long as we are in contract with the customer but once the contract ends we will delete the SPs and other items we implement as part of DB management.