Learn more about SQL Server tools


Latest from MSSQLTips

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

SQL Server 2016 T-SQL Syntax to Query Temporal Tables

Troubleshoot SQL Azure Bacpac Import Failures

On-Demand Webcast - Monitoring SQL Server with Mobile Devices

Configure the SQL Server Integration Services For Loop Container

On-Demand Webcast - Performance Monitoring with Uptime

On-Demand Webcast - Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

On-Demand Webcast - Encrypting Data with SQL Server

On-Demand Webcast - Performance monitoring with Spotlight on SQL Server

Whitepaper - Resolving the Database Performance Blame Game

3 Tips for Managing Large Numbers of SQL Server Jobs

Accelerate SQL Server with Flash Storage

Decrypting SQL Server database objects

MSSQLTips author Greg Robidoux By:   |   Read Comments (7)   |   Related Tips: More > Encryption

>>> >> > Vote for your favorite MSSQLTips.com Authors < << <<<

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.

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.

FROM authors

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.


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:

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: 8/23/2006

About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Monday, May 19, 2014 - 4:47:36 AM - muthupandi Read The Tip

Function does not work.....

Thursday, May 02, 2013 - 8:57:41 PM - Alan Read The Tip

I have tried this decryption procedure and the encryted procedure I tried to decrypt with this code has vanished.


why is that, where has it gone etc

Tuesday, January 11, 2011 - 10:32:49 AM - Greg Robidoux Read The Tip

What version of SQL Server are you using?

Tuesday, January 11, 2011 - 4:56:20 AM - Prasad Read The Tip

the procedure doesnt work

Tuesday, August 31, 2010 - 2:11:22 PM - grobido Read The Tip
It looks like the above link no longer exists.  You can try to use this link instead:


Monday, April 20, 2009 - 1:40:40 PM - grobido Read The Tip

Take a look at this other code in the link below.  There was a comment made on the originating site.


Not sure if this works with SQL 2005 or not.  Have not tested it.


Monday, April 21, 2008 - 9:57:30 PM - vvkp Read The Tip


When I use your script for decryption, the stored procs are disappearing. I am using your took for SQL2005. Any idea?

Sponsor Information



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.