Recovering lost stored procedure source code in SQL Server

Three common scenarios

Lost source usually arrives in one of three shapes, and each has a slightly different priority order.

• Inherited system. A database was handed over without its repository. You need to understand and change procedures that are still running.
• Vendor departure. The developer or consultancy that built the system has left. Their copy of the source is unavailable or disputed.
• Audit or migration. A reviewer, an upgrade or a cloud migration requires a readable definition of every module, including those never committed to source control.

In all three cases the goal is the same: get a trustworthy plain-text definition into a repository, so the encrypted copy in the database stops being the only record.

Check these sources first

1. Backups. Restore a copy of the database to a non-production instance and inspect the modules there. An older backup may contain the definition if the module was encrypted later.
2. Deployment artifacts. Look for DACPACs, BACPACs, SSDT projects, migration scripts, installer packages and release notes in build systems or shared drives.
3. Source control history. Search all branches and tags, not just the current default branch. Check archived repositories and personal forks.
4. Other environments. Development, test and staging instances sometimes hold an unencrypted or older version of the same procedure.
5. The vendor or original developer. If a third party supplied the module, your support agreement may entitle you to the source.
6. Documentation. Design documents, data dictionaries and change tickets occasionally contain the full text of older versions.

Only when all of these are exhausted should decryption become the plan.

When decryption is the remaining option

Decryption assumes you own the database or have explicit permission to administer it. Confirm the following before you start:

• The database owner has approved recovery, and the approval covers the specific objects.
• You have a sysadmin login for the instance, and DAC access where SQL Server requires it.
• You are working on a copy, or during an approved change window, if the instance is production.
• You know where the recovered output will be stored and who can access it.

The mechanics are covered in how to decrypt a stored procedure. For a whole database, use a batch run rather than repeating the process object by object.

Verify before you trust

Recovered code should be treated as a draft until it has been checked.

• Compile it. Create the module in a non-production copy and confirm there are no syntax or dependency errors.
• Compare behaviour. Run representative calls against both versions where possible.
• Check session settings. SET ANSI_NULLS and SET QUOTED_IDENTIFIER are stored with the module and affect behaviour.
• Review dependencies. Confirm the objects it references still exist with the expected names and schemas.
• Have a second person read it. A recovered procedure is often the first time anyone has reviewed the logic in years.

After recovery: close the loop

1. Commit the recovered definitions to version control with a message explaining where they came from.
2. Tag the commit with the date and the source instance, so the provenance is clear.
3. Update the deployment process so future changes come from the repository, not from direct edits.
4. Add the objects to your documentation and ownership records.
5. Decide deliberately whether to keep WITH ENCRYPTION on future deployments. If you keep it, keep the plain-text source as well.

A prevention checklist

• Every module definition is in version control, including encrypted ones.
• Deployments are generated from the repository, with a review step.
• Handover checklists include repository access and a list of encrypted objects.
• Encrypted modules are documented with an owner and a recovery authority.
• Backups are tested by restoring them, not just by confirming they exist.

A note on third-party software

Recovering the source of a commercial application you licensed is not the same as recovering your own code. Many licences prohibit reverse engineering, and the permission you have to run the software does not automatically extend to reading its implementation. Ask the vendor first, and get the answer in writing. The authorized-use policy explains where AzSQL Technology draws that line.