Encrypt stored proceudres with password.

Carlos D 46 Reputation points
2020-09-30T15:23:19.3+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-09-30T21:55:20.607+00:00

    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.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2020-10-01T08:19:48.61+00:00

    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.

    0 comments
  2. Carlos D 46 Reputation points
    2020-10-01T12:11:46.907+00:00

    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.

    0 comments
  3. Tom Phillips 17,716 Reputation points
    2020-10-01T18:32:33.737+00:00

    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.

    0 comments
  4. Carlos D 46 Reputation points
    2020-10-01T18:40:06.053+00:00

    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.