Encrypt SQL statements

If you distribute your SQL code to third parties (perhaps in runs on other peoples servers), you may wish to help protect your intellectual property by encrypting your SQL statements.

Simply using "WITH ENCRYPTION" at the end of your normal CREATE PROCEDURE will stop the definition of your sql statements being returned. The statement will be automatically decrypted on the fly.


CREATE PROCEDURE AlexTestProcedure
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT Firstname, Surname, Email
FROM Users;
GO


After having a quick look around the internet on the merits of the added security;
In SQL server 2000, the de-obfuscated text is stored in the SYSCOMMENTS table before it is executed. So you may wish to consider this as a preventative measure for the casual nosey parker, but not a bullet proof solution. However the equivalent MSDN page for SQL 2008 R2 doesn't mention this.

MSDN article on CREATE PROCEDURE sql server 2008 r2

MSDN article on created procedure sql server 2000

Interesting things you learn whilst talking to DBA's (thanks Solomon!)