Encrypting Stored Procedures in SQL Server

When you are creating a Stored Procedure is SQL Server, you can use 'WITH ENCRYPTION' argument that will obfuscate the original text of the CREATE PROCEDURE statement. You will not be able to directly get the original text of the CREATE PROCEDURE statement from the Stored Procedure, so be sure to save the CREATE PROCEDURE statement somewhere to be able to edit or reuse it later if needed.

To create encrypted Stored Procedure use the 'WITH ENCRYPTION' argument as shown in the following example:

CREATE PROCEDURE dbo.encrypted_sp_example
SELECT * FROM Person.AddressType

In the Stored Procedures list in Management Studio, you can see a padlock icon next to the Stored Procedure which means that it has been encrypted using the WITH ENCRYPTION argument:


Execute the procedure with 'Include Actual Execution Plan' option turned on:

EXEC dbo.encrypted_sp_example

...and you will see that the Execution Plan is not included in the results for encrypted Stored Procedures:


If we want to see the definition of the stored procedure created with the WITH ENCRYPTION argument:

EXEC sp_helptext 'dbo.encrypted_sp_example'

...we will get the following message:

The text for object 'encrypted SP name' is encrypted.


If we try to script the encrypted Stored Procedure:
Right click on the Stored Procedure -> Script Stored Procedure as -> CREATE To -> New Query Editor Window:


...we will get the following error message:

Property TextHeader is not available for StoredProcedure 'stored_procedure_name'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.


If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">