WITH ENCRYPTION in SQL Server: what it protects, and what it does not

What WITH ENCRYPTION does

WITH ENCRYPTION is an option on CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW and CREATE TRIGGER. It tells SQL Server to store the module definition in a form that the normal metadata surfaces cannot read back as plain text.

The option applies to the definition of the module. It has no effect on the data stored in your tables, and it does not change who can execute the module.

CREATE PROCEDURE dbo.uspGetAuthors
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;
    SELECT AuthorID, AuthorName
    FROM dbo.Authors;
END;
GO

What you see in the catalog afterwards

Three common ways of reading a module stop working once the module is encrypted.

• sp_helptext returns the error “The object comments have been encrypted.”
• OBJECT_DEFINITION() returns NULL.
• sys.sql_modules.definition returns NULL, while OBJECTPROPERTY(object_id, 'IsEncrypted') returns 1.

SELECT m.definition,
       OBJECTPROPERTY(o.object_id, 'IsEncrypted') AS is_encrypted
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.object_id = OBJECT_ID('dbo.uspGetAuthors');

Note that NULL from OBJECT_DEFINITION is not proof of encryption on its own. The function also returns NULL when the object does not exist or when the caller lacks permission to see its definition. Check IsEncrypted to distinguish the two cases.

What it does not protect

It is worth being precise about the security boundary, because teams sometimes assume more than the feature provides.

• Table data is unaffected. WITH ENCRYPTION is not encryption at rest. If you need data-at-rest protection, look at Transparent Data Encryption or Always Encrypted.
• Execution permissions are unchanged. Granting EXECUTE still lets a caller run the module; the option does not restrict access.
• It is not a boundary against administrators. A principal with sysadmin rights can recover the definition through supported administrative access. Treat the option as a deterrent against casual reading, not as a secret that an administrator cannot reach.
• It does not hide the object's name, parameters or dependencies, which remain visible in the catalog.

Rule of thumb: if a value must never be read by a DBA, it should not be embedded in a module definition at all. Use a key store or a separate security boundary instead.

When the option makes sense

There are legitimate uses. A vendor may encrypt modules delivered into a customer's environment to discourage casual edits that would make support harder. An internal team may encrypt a module to signal “read the deployment source, not this copy”.

What it is not good for is protecting credentials, licence logic, or anything else where a determined administrator must be prevented from reading the code. Those requirements need a different design.

The operational cost

Encryption makes a module opaque to the tooling DBAs rely on. In practice that means:

• No meaningful diff of the module in source control comparisons.
• Harder upgrade and migration work, because the current logic must be recovered before it can be changed.
• Audit friction when a reviewer needs to confirm what a procedure actually does.
• A permanent risk that the only copy of the source is the deployed, encrypted object.

If you use the option, keep the plain-text definition in version control and deploy it from there. The encrypted copy in the database should never be the source of record.

How a DBA recovers a definition

When the source has been lost, work through the options in order of least risk:

1. A backup or deployment package. Restore a copy, or find the build artifact that created the object.
2. Source control or a developer's working copy. Check branches, tags and archived repositories.
3. The vendor. If a third party supplied the module, ask them for the source under your support agreement.
4. A documented decryption workflow on an instance you own, using a database owner's authorization and an administrative login. See how to decrypt a stored procedure.

Each of these assumes you are entitled to the code. If the module belongs to a third party, get written permission before recovering it.

Summary

WITH ENCRYPTION hides a module definition from everyday metadata access. It does not encrypt data, does not control execution rights and does not stop an administrator. Use it as a deterrent, keep the real source in version control, and document who holds the authority to recover it.