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

Execute dynamic sql in funtion

Hello friends ,

this is probably one my post which took me so long to varify but negligible time to write it .

We cannot execute dynamic sql in a user defined funtion.

SET @query  = ‘select * from nytable’

exec (@query)  —   this will not work 

neither you can call any stored procedure from a function(except extended stored procedures).

the another possible workaround for this is that you create a stored procedure rather than a UDF or create an extended stored procedure . than you can call it as

exec sp_executesql myextendedSP