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.

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.

We can easily see the script for the stored procedure.

Now let us run encrypted stored procedure.

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)