How to Encrypt a Stored Procedure in SQL Server

Posted on by Ian

In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption.

To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument.

You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE.

When you encrypt a stored procedure in this way, the procedure’s text is converted to an obfuscated format. Its definition is not directly visible in any catalog views. Therefore, the procedure’s definition cannot be viewed by users that have no access to system tables or database files.

Example 1 – Create an Encrypted Stored Procedure

Here’s an example of creating an encrypted stored procedure.

CREATE PROCEDURE dbo.usp_GetCatsByName @catname varchar(70)
WITH ENCRYPTION
AS
SELECT 
    CatId,
    CatName,
    Phone
FROM dbo.Cats
WHERE CatName = @catname;

GO

The part for encrypting it is WITH ENCRYPTION. I could simply remove that argument if I didn’t want to encrypt it.

Example 2 – View the Result

After creating that procedure, now when I use the sp_helptext stored procedure to view the procedure’s definition I get a message telling me that it’s encrypted.

EXEC sp_helptext 'usp_GetCatsByName';

Result:

The text for object 'usp_GetCatsByName' is encrypted.

And if I use the sys.sql_modules system catalog view I get NULL.

SELECT definition 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.usp_GetCatsByName');

Result:

+--------------+
| definition   |
|--------------|
| NULL         |
+--------------+

I get a similar result, regardless of which T-SQL method I use to try to get the procedure’s definition.

And here’s the error message I get in Azure Data Studio when I try to script the procedure:

No script was returned when scripting as Create on object StoredProcedure

And I would get a similar message if I tried to view it in SSMS, DBeaver, or any other GUI database management software.

Example 3 – Add Encryption to an Existing Stored Procedure

If you want to encrypt an existing stored procedure, use ALTER PROCEDURE with the same definition. In other words, I can take the first example, and replace CREATE with ALTER.

ALTER PROCEDURE dbo.usp_GetCatsByName @catname varchar(70)
WITH ENCRYPTION
AS
SELECT 
    CatId,
    CatName,
    Phone
FROM dbo.Cats
WHERE CatName = @catname;

GO

This obviously assumes that the rest of the procedure’s definition is exactly the same as the existing one.

The easiest way to ensure that you’re using the same definition is to use your GUI tool to script the existing procedure using the “Script as Alter” option, if it exists. Otherwise you could use “Script as Create”, then when the definition appears, change CREATE with ALTER.

If you only have a command line interface, you could query the sys.sql_modules view to get the existing definition (like in the previous example). You can then copy the definition and replace CREATE with ALTER.

Once you’ve done that, you can add WITH ENCRYPTION and run it again.

Example 4 – Remove Encryption from a Stored Procedure

We can remove encryption by running the ALTER PROCEDURE statement without the encryption option.

ALTER PROCEDURE dbo.usp_GetCatsByName @catname varchar(70)
AS
SELECT 
    CatId,
    CatName,
    Phone
FROM dbo.Cats
WHERE CatName = @catname;

Note that this isn’t the same as decrypting the stored procedure. Here, we’re simply altering the existing procedure to the new definition. So it assumes that you already have a copy of the existing procedure somewhere in your source control.

Example 5 – Natively Compiled Stored Procedures

Encryption isn’t supported on natively compiled stored procedures.

Here’s what happens when I try to encrypt a natively compiled stored procedure:

ALTER PROCEDURE [dbo].[usp_GetCowsByName] @cowname varchar(70)
WITH SCHEMABINDING, NATIVE_COMPILATION, ENCRYPTION
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
    LANGUAGE = N'us_english'
    )  
SELECT 
    CowId,
    CowName,
    Phone
FROM dbo.Cows
WHERE CowName = @cowname
END;

Result:

Msg 10794, Level 16, State 17, Procedure usp_GetCowsByName, Line 3
The option 'ENCRYPTION' is not supported with natively compiled modules.

That example procedure is taken from my article, How to Create a Schema Bound Stored Procedure in SQL Server, which also explains that you also can’t schema bound a stored procedure if it’s not a natively compiled procedure.

Important Notes

Here are some things you should know about encrypting stored procedures in SQL Server:

  • Privileged users that can access system tables over the DAC port or directly access database files will still be able to view the stored procedure (non-encrypted) definition.
  • Users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime.
  • Using encryption prevents the procedure from being published as part of SQL Server replication.
  • CLR procedures cannot be encrypted.
  • Natively compiled procedures cannot be encrypted.