How to find encrypted objects in a SQL Server database

A catalog query that lists encrypted modules

Encrypted modules live in the same catalog views as everything else, but their definition column is empty. The query below lists every user module in the current database whose definition cannot be read, and flags the ones SQL Server reports as encrypted.

SELECT s.name              AS schema_name,
       o.name              AS object_name,
       o.type_desc,
       o.create_date,
       o.modify_date,
       OBJECTPROPERTY(o.object_id, 'IsEncrypted') AS is_encrypted
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
  AND o.is_ms_shipped = 0
  AND (m.definition IS NULL
       OR OBJECTPROPERTY(o.object_id, 'IsEncrypted') = 1)
ORDER BY s.name, o.name;

Run it in each database you are responsible for. The type codes are: P stored procedure, V view, TR trigger, FN scalar function, IF inline table-valued function, TF multi-statement table-valued function.

Interpreting the results

A row in this list does not automatically mean the module is encrypted. The is_encrypted column is the reliable signal. Here is why a definition can be NULL for other reasons:

• Insufficient permission. Metadata visibility hides definitions from logins that do not have rights on the object. Connect with an administrative login when you audit.
• CLR modules. Objects implemented in a .NET assembly have no T-SQL text to return.
• Natively compiled modules. Some In-Memory OLTP procedures are stored differently and are not ordinary T-SQL modules.
• System objects. Filtering with o.is_ms_shipped = 0 excludes Microsoft-shipped objects, which are also not what you are looking for.

If is_encrypted is 1, the module was created with WITH ENCRYPTION. If it is 0 or NULL while the definition is empty, check your permissions before drawing conclusions.

Counting encrypted objects per database

For an estate review, a simple count per database is often enough to decide where to spend time. Run this in each database:

SELECT DB_NAME() AS database_name,
       COUNT(*) AS encrypted_modules
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1;

To survey many databases on one instance, generate the same statement for each database name and run the results, or connect to each database in turn. Avoid writing into production databases while auditing.

Exporting the list for review

Save the result set from SQL Server Management Studio as CSV, or paste it into a spreadsheet. For each object, record:

• The schema-qualified name and object type.
• Whether a plain-text definition exists in source control.
• The business owner or application that depends on it.
• Whether recovery is permitted, and by whom.

This turns a technical inventory into a decision record. It also answers the question an auditor will ask: not just “which objects are encrypted” but “who is allowed to see them”.

Next steps

Once you know what is encrypted, check whether the source already exists somewhere else. Backups, deployment packages and source control are lower-risk than decryption. If the source is genuinely lost, see how to decrypt a stored procedure for a documented workflow.

To process an entire database at once, AzSQL Script Decryptor lists encrypted objects directly and can recover them in a single run. See the product overview.