Decrypting Stored Procedures The Cumbersome Way

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Setup

I tested this on SQL Server 2017/2019/2022. I don’t have older instances handy, so I can’t verify that for those. However, I ran this code in databases on each instance:

CREATE OR ALTER PROCEDURE dbo.DecryptionTest
WITH ENCRYPTION
AS
SELECT 2 AS Two;
DECLARE @i INT = 1;
IF @i = 2
   SELECT 3 AS Two;
ELSE
   SELECT 2 AS Two;
GO
CREATE PROCEDURE [dbo].[DecryptionTest2]
WITH ENCRYPTION
AS
   SELECT 2 AS Two;
GO

I had shown how to detect these are encrypted in a previous post.

Decryption

I had initially sent the client these links, since I was sure they’d worked at one point. I thought that SQL Server 2000 and earlier had a different algorithm. Don’t quote me on that and there’s so much Google-noise, I can’t verify this.

• https://www.sqlservercentral.com/blogs/simple-way-to-decrypt-sql-server-stored-procedure
• https://www.sqlservercentral.com/scripts/decrypt-stored-procedures-views-and-triggers

Those links don’t work, and I can only guess that either I had used different procs or these were edited.

In any case, I found a gist here: https://gist.github.com/jstangroome/4020443

This has code that does work. I ran this on my instances, and I got similar output to this on all instance:

I didn’t clean this or try to extract the value from the XML, but for the client this worked.

It’s not the cleanest or best way, but it does decrypt the procs I’ve created.