Steve Jones breaks the connection:
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.
Stored procedure encryption is one of the more annoying features in SQL Server. The idea was, if you wanted to prevent end users from reading your code, you could encrypt the procedures. But in order to use the procedures, SQL Server needed to decrypt them and you needed this to work on restored backups, so the decryption keys needed to be available to that SQL Server. The infrastructure is a bit different from how Microsoft eventually landed Transparent Data Encryption, enough so that it turned out breaking these procedures is trivial, as Steve shows.
I didn’t know that SQL Compare did decryption. The couple of times I needed to do this, I had used a standalone tool which was released in the 2005 timeframe, so it’s good to see something still supported which does this.