Note : Please save your original script before encrypting it .
PROCEDURE encryptTest WITH ENCRYPTION AS
SELECT ‘Test’— comment: sp_password END
— result is : foo
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