Encrypt SQL script on Production Server — Protecting Database

Note : Please save your original script before encrypting it .

CREATE  

 

PROCEDURE  encryptTest WITH ENCRYPTION AS

BEGIN  

 

 

SELECT   ‘Test’— comment: sp_password END

exec

 

encryptTest

— result is : foo

exec 

 

sp_helptext ‘encryptTest’— result is :The text for object ‘encryptTest’ is encrypted.

— Points to be noted before encrypting ant script

— (1) make sure you keep the logic of the stored procedure in a safe place,

— since you won’t have easy access to the procedure’s code once you’ve saved it

— (2) there are at least two ways to defeat this mechanism. One is to run SQL Profiler while executing

— the stored procedure; this often can reveal the text of the procedure itself, depending on what the

— stored procedure does (e.g. if it has GO batches, dynamic SQL etc). If they miss the initial install,

— the user can delete the stored procedures or drop the database, start a Profiler trace, and ask you to

— re-create them (in which case they will capture the CREATE PROCEDURE statements). You can prevent Profiler

— from revealing the text to snoopers by embedding sp_password in the code, as a comment:

— (3) These encrypted scripts will not be in updated in the process of replication

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s