Problem
SQL Server offers a way to encrypt your Stored Procedures to make sure that prying eyes cannot see what is going on behind the scenes. The problem with this method is that it is not a very secure way of encrypting the contents of your stored procedures. In addition, since SQL Server basically stores your source code vs. a compiled version most people rely on the code that is in the database server instead of moving the code to a source control application. Because of the need to access this code this tip outlines various methods of decrypting your encrypted database objects.
Solution
There are basically two ways that you can go about doing this; 1) you can write your own process or 2) you can download and/or purchase a tool that was developed to do just this. Various tools exist that allow you to decrypt your database objects.
So how does encryption work?
Basically it is a simple option that you use when creating your objects. Here is a basic example using the "WITH ENCRYPTION" option.
| CREATE PROCEDURE uspGetAuthors WITH ENCRYPTION AS SELECT * FROM authors GO |
After I create this stored procedure, when I try to view the contents of the stored procedure using the following command,
| sp_helptext uspGetAuthors |
I get this error: "The object comments have been encrypted."
Also, when trying to look at this stored procedure in Enterprise Manager I get the following error message.
To decrypt the stored procedure I used this script. This is very simple to install and use and allows you to decrypt stored procedures, view and triggers and best of all it is free. To use this script I first created it and then ran the following command.
| exec DECRYPT2K 'uspGetAuthors', 'S' |
When I issue the sp_helptext statement again this is the results I get, so we can see that it decrypted the stored procedure and it is now available for editing both with Query Analyzer or Enterprise Manager.
| CREATE PROCEDURE uspGetAuthors AS SELECT * FROM authors |
So as you can see there is not a lot to decrypting your database objects. Also, encrypted objects are not really that secure, so this should not be your only method for securing code that you do not want others to see.
Along with the script mentioned above, here is a list of other tools that are available:
- DecryptSQL
- Orbital's SQL Decryptor
- SQL Script Recovery Pro
- SQL Object Decrypter
- Various tools from SQL-Shield
- And the script mentioned above
Next Steps
- Add this tool to your SQL toolkit
- Take a look at some these tools that are available
- Make sure that if you are trying to keep prying eyes away from your code using the encryption option that there are ways to get to it so look for other methods
Last Update: 2006-08-23
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
View all my tips
