How to Decrypt a Stored Procedure in SQL Server

1. Confirm you are authorized

Before running any query, establish that you are entitled to see the definition. You should own the database, administer it as part of your role, or hold written permission from its owner. If the module belongs to a third-party application, check that application's licence: recovering its source may be prohibited even when you control the server.

Write down the authorization and the scope. It takes a minute and answers the question a change reviewer or auditor will ask later.

2. Confirm the procedure is encrypted

Not every procedure that resists inspection is encrypted. Check the property and the definition in one query:

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

If is_encrypted is 1, the module was created with WITH ENCRYPTION. If it is 0 but definition is NULL, you probably lack permission — reconnect with an administrative login before going further.

3. Look for a recoverable source first

Decryption should be the last option, not the first. A restored copy of an older backup, a deployment script, a DACPAC or a developer's working copy is easier to trust and easier to audit than recovering from the live instance. Work through the list in recovering lost source code before you decrypt.

4. Decrypt with a documented tool

If the source is genuinely gone and you are authorized, use a tool that makes the operation repeatable and reviewable. In AzSQL Script Decryptor the workflow is:

Connect to the instance with a login in the sysadmin server role. On SQL Server 2005 and later, confirm that the DAC is available if the instance requires it.
Select the database and open the decryption wizard. The object list is filtered to encrypted modules.
Choose the procedure — a single object or the whole set.
Decrypt and review the output in the editor, with T-SQL syntax highlighting.
Save or print the recovered script. Prefer saving to a file you will commit to source control.

The product page covers supported SQL Server versions and object types. The free trial shows the first 500 characters of each object, which is enough to confirm that the workflow works on your instance before you buy a licence.

5. Verify the recovered definition

A recovered script is evidence, not automatically a correct build artifact. Before you rely on it:

Read it end to end and compare it with how the procedure behaves in production.
Check the header: SET ANSI_NULLS and SET QUOTED_IDENTIFIER settings matter for some modules.
Compile it in a non-production copy of the database.
Run representative queries and compare results with the current behaviour.
Diff it against any older version you can find, to see what changed while the source was lost.

Only then should the recovered script become the source of record.

6. What not to do

You will find advice online about reading undocumented system tables directly to extract module text. We do not recommend it:

The storage layout is internal and can differ between SQL Server versions and builds.
Queries against system tables are unsupported and may fail after an upgrade.
There is no audit trail, no verification step, and no repeatable process for the next object.
On a production instance, ad-hoc access to internal structures carries operational risk for no benefit over a supported workflow.

A documented tool, an approved change and a saved output file are easier to defend in a review.

7. Prevent it from happening again

Recovery is a fix for the past. The durable fix is process:

Keep every module definition in version control, encrypted or not.
Deploy from the repository, and never edit an encrypted module directly in production.
Record which modules are encrypted and who holds the authority to recover them.
Include encrypted objects in handover checklists when systems change owners.

How to decrypt a stored procedure in SQL Server