Encrypt / Decrypt Stored Procedures

Jeff S
Jeff S used Ask the Experts™
on
How do I encrypt my stored Procedures and additionally decrypt them if needed to make any changes. I am finding myself wanting to use encryption on some of my stored procedures.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

...

http://www.mssqltips.com/tip.asp?tip=1046
You can encrypt stored procedures by adding the WITH ENCRYPTION clause, but like stated above it's rather weak and you can find scripts to crack it with a simple google search.  Another downside is that you have to maintain a clear text version of the stored procedures some where, preferably source control, because SQL doesn't innately provide a mechanism to decrypt it.

Greg

 
Commented:
you can encrypt your stored procedures as JestersGrind mentioned "with encryption" command..

to decrypt it you can use the tool SQL Decryptor which you can find in the following link and it's FREE!
http://optillect.com/products/sqldecryptor/overview.html
1. You cannot edit ENCRYPTED PROCEDURE from DB - keep the source in the file.
2. To make a PROCEDURE ENCRYPTED use:

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial