A migration team can script thousands of objects and still miss the modules created WITH ENCRYPTION. Encrypted stored procedures and similar modules have NULL definition text in sys.sql_modules, so normal scripting and comparison cannot recover their bodies. Find them early and obtain the approved source before moving the database.
Know What WITH ENCRYPTION Does
WITH ENCRYPTION obscures a module definition from ordinary catalog queries and SSMS scripting. It is not a substitute for protecting data or a complete security boundary. The practical migration problem is simple. The engine has an executable module, but the team lacks reviewed source text to recreate it elsewhere. A database backup can preserve the object on the same compatible platform, but a schema migration tool needs source.
I ask the vendor or owning team for the exact definition, version, dependencies, and permission to deploy it in the target. If that source cannot be obtained, the migration has a functional gap that must be resolved before cutover. What application call depends on the hidden module?
Inventory Encrypted Stored Procedures and Other Modules
sys.sql_modules has rows for T-SQL procedures, functions, views, and triggers. A NULL definition can also reflect metadata visibility, so use an account with appropriate permission and check the encrypted object property. Include schema, type, and create and modify dates in the output. The same query lists encrypted stored procedures, functions, views, and triggers in one pass.
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS object_name, o.type_desc,
o.create_date, o.modify_date,
CONVERT(int,OBJECTPROPERTYEX(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 m.definition IS NULL
AND CONVERT(int,OBJECTPROPERTYEX(o.object_id,'IsEncrypted')) = 1
ORDER BY o.type_desc, schema_name, object_name;Run this in every database in scope. Server-level triggers and objects outside the database need their own inventory. If the query returns nothing under a low-privilege account, repeat it under an authorized account before declaring the estate clear. A NULL definition alone is not a definitive encryption test.
What Scripting Cannot Recover From Encrypted Stored Procedures
SSMS Generate Scripts and OBJECT_DEFINITION cannot provide the hidden body of an encrypted module to a normal authorized reader. A schema compare therefore cannot prove whether the target contains the same logic. A source-control repository or vendor installation package can hold the original script, but its version must be matched to the object currently running.
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.uspVendorBilling'))
AS module_definition;For an encrypted example, the result is NULL. For an unencrypted object, NULL can still mean insufficient permission or a wrong name. Check the inventory row and permissions before drawing a conclusion. Do not treat a successful database backup as a substitute for a deployable source definition.
Map the Failure by Object Type
An encrypted procedure can be called by application code but cannot be recreated from catalog text. An encrypted function can sit inside computed columns, views, or queries; migration tools can miss the dependency body. An encrypted view can hide joins and security filters. An encrypted trigger can enforce side effects that a data copy alone does not reproduce.
I list each object type with its callers, inputs, outputs, permissions, and business owner. Some dependency metadata is incomplete for encrypted definitions, so application traces and vendor documentation matter. A target database that accepts the tables but lacks a trigger can appear healthy until the first write produces a wrong result.
Recover Approved Source for Encrypted Stored Procedures
Ask the vendor for an installation script or source package matching the deployed version. Ask internal teams for the release artifact and compare create/modify dates, signatures, known behavior, and version tables. If source is unavailable, negotiate a replacement or remove the dependency through an approved application change. Avoid unsupported attempts to extract hidden text from memory as a migration plan.
The source package should include required SET options, permissions, dependency order, and any target-specific changes. Store it in the organization's controlled source system with a version identifier. A vendor script that creates a different version is not proof of equivalence merely because it has the same object name.
Test the Target Behavior
Azure SQL Database and other target platforms can differ in supported T-SQL features, cross-database references, SQL Agent usage, and security context. Compile the recovered source in a test target and run meaningful input-output tests. Do not assume a WITH ENCRYPTION module can simply be copied as a binary object into a different service.
I compare result sets, side effects, error handling, and performance for representative calls. For a trigger, test inserts, updates, and deletes that should fire it. For a function, test boundary inputs and calling queries. The test should reveal whether the recovered source is the exact working logic or an older vendor release.
Make Cutover Conditional on Coverage
Keep an inventory row for every hidden module: object name and type, owner, source location and version, dependencies, target status, test evidence, and cutover decision. A missing definition is a migration blocker for that feature until an approved replacement is ready. Do not hide it in a generic “manual objects” note.
A complete migration rehearses creation from source on a clean target and verifies every module exists afterward. I keep the before and after counts and call tests with the release record. The earlier the hidden objects are found, the more time the team has to recover source without delaying the final move.
Check Permissions and Source Provenance
A NULL module definition is a clue, not a verdict by itself. The account querying metadata needs permission to view it. Run the encrypted-property test under an approved privileged account and compare with deployment artifacts. A source file recovered from an old laptop is not automatically the deployed version. Validate its release date, object signature, parameters, and behavior against the running instance before using it for a target build.
Keep Vendor Dependencies Visible
A vendor procedure can call another encrypted module, use a linked server, or depend on an Agent job. The source request should cover the whole dependency set, not one object named in an error. Ask whether the vendor supports the target SQL Server version or Azure SQL service and whether a new package is required. I keep the answer and the tested package version in the migration inventory so the cutover team knows what is supported.
Related reading on this blog: How to Hide Stored Procedure's Code? WITH ENCRYPTION: Interview Question of the Week #088 and The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON: Experts Opinion.
An encrypted module is not a recoverable script, it is a reason to locate an approved source.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
