How to Hide Stored Procedure’s Code? – WITH ENCRYPTION – Interview Question of the Week #088

Question: How to Hide Stored Procedure’s Code so anyone with access to stored procedure cannot see it?

Answer: WITH ENCRYPTION.

How to Hide Stored Procedure's Code? - WITH ENCRYPTION - Interview Question of the Week #088 encryptionsp3

This is one of the very popular question, I often hear. Today I decided that I will include this in the next interview question of the week. Well, one word answer is you can hide it with the command WITH ENCRYPTION when you create stored procedure or function. Let us see this particular command in action.

First, let us create a simple stored procedure.

CREATE PROCEDURE RegularSP
AS 
SELECT 5 AS 'FIVE'
GO

Now let us create another stored procedure WITH ENCRYPTION.

CREATE PROCEDURE EncryptedSP
WITH ENCRYPTION
AS 
SELECT 5 AS 'FIVE'
GO

Now let us run following command to see the text of the stored procedure.

First let us run sp_helptext for RegularSP.

How to Hide Stored Procedure's Code? - WITH ENCRYPTION - Interview Question of the Week #088 encryptionsp1

We can easily see the script for the stored procedure.

Now let us run encrypted stored procedure.

How to Hide Stored Procedure's Code? - WITH ENCRYPTION - Interview Question of the Week #088 encryptionsp2

In this case we are not able to see any code, but just a message that SP is encrypted.

You can clearly see in the example above that we can see the text of the regular stored procedure, but in the case of the encrypted stored procedure we are not able to see the code. It is not possible to change the code of the encrypted stored procedure unless you have original code which has created the stored procedure.

Well, now you know how to Hide Stored Procedure’s code.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
How to Find Recent Executed Queries in SQL Server? – Interview Question of the Week #087
Next Post
How to Find Missing SQL Server Configuration Manager? – Interview Question of the Week #089

Related Posts

7 Comments. Leave new

  • Aaron Olson (@aarondolson)
    September 11, 2016 5:35 pm

    Everything we do these days is with a “security first” mindset, did not even think about the use-case for encrypting a stored procedure.

    Reply
  • Just do not use this to hide confidential information. It can be decrypted in two seconds using the code that you can google.

    Reply
  • There are many tools to decrypt stored procecure (even free, “decrypt all objects in db”…).

    Cons is: Missing info (procedure name, statements) in permormance monitoring views/tools.

    TL;DR: Don’t do it.

    Reply
  • Very true Aaron!

    Reply
  • How to update.

    Reply
  • hi,

    I know with “with encryption” option we are only obfuscate our procedures and function. then there are tools like dbforge that can decrypt them.

    is there a way to encrypt stored procedure and functions with keys and password to block decryption (or make it really hard to decrypt).

    Thanks,
    Sami

    Reply

Leave a Reply

Menu