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
WITH ENCRYPTION
AS
SELECT * FROM Person.AddressType
GO

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:

encrypted_sp_icon

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:

execute_encrypted_sp_execution_plan_on

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.

sp_helptext_encrypted_sp

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:

encrypted_sp_script_new_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.

encrypted_sp_script_new_window_error