As part of work that I was doing recently, I had to generate the creation script for a SQL Server database. While running through the Generate Script task in Management Studio, I received an error that indicated that the stored procedure was encrypted. Never having running into encrypted stored procedures before, I was a little surprised. It turns out that encrypted stored procedures can’t be scripted. Could be that they’re encrypted. Duh!
Anyway, the documentation says that, for encrypted stored procedures, it is important to generate the creation script prior to encryption. So I started asking the client if they had the creation scripts. It turns out that a previous DBA had invoked the annoying ‘job security’ clause when creating the stored procedures, so the creation scripts were nowhere to be found.
Some additional research on the problem and I find a third party component that can decrypt the stored procedures. It’s called SQL Decrypter and it’s from Orbital Technologies (http://www.orbitaltechnologies.org/decryptor/). I have to say, it’s a nice tool. Does exactly what it’s supposed to do, quickly, easily and painlessly. So the client gets the generation scription and Orbital gets another (I’m sure) satisfied customer.