I'm currently based in Lincoln, England due to COVID-19.

Decrypting stored procedures in SQL Server

Monday, 06 April 2020

I came across an issue recently where I lost the code to a stored procedure created a few years ago, usually our stored procedures are not encrypted however this one was.  I've never thought about being able to decrypt a stored procedure, as it kind of feels against the point of encrypting it in the first place - but last week I found out that you could indeed decrypt an encrypted stored procedure, and it's actually super easy. 

All you need to do is connect to DAC via SMSS on your SQL Server by clicking on Database Engine Query.  This looks like any normal database connection but it's much cooler, when the window pops up add admin: to the start of your server name, for example: admin:localhost. Once in DAC, paste in the below and edit Line 7 to your schema and procedure name and put USE [DatabaseName] at the top of the script.

Now ideally you should only be decrypting procedures you have the right to decrypt (procedures you created or have been given permission to access), but this is super useful if you need to get sql code that you no longer have saved.