Protect your sensitive information

Business rules are sometimes considered confidential, both between businesses and between personnel within an organization. Business rules expressed as SQL code can be seen as equally sensitive. SQL Server has allowed for the protection of confidential rules by supplying you with an encryption option for use in stored procedures, views, and triggers. If you have rules or processes that you do not want exposed to others, then use the WITH ENCRYPTION clause in the CREATE statements of procedures, views, and triggers.

 

CREATE PROCEDURE pCalcPayroll

WITH ENCRYPTION

AS

 

Or

 

CREATE VIEW pPayrollReport

WITH ENCRYPTION

AS

 

Or

 

CREATE TRIGGER tu_CalcPayRate ON payroll WITH ENCRYPTION 

FOR UPDATE

AS

 

This option will encrypt the syscomments system table so that no one, including the system administrator, can see the code behind these SQL Server objects. When using the sp_helptext on any one of these encrypted objects, the user will be presented with the following message.

 

The object's comments have been encrypted.

 

If these objects were not encrypted, sp_helptext would display all of the SQL code regardless of confidentiality concerns.

 

The drawbacks to using the WITH ENCRYPTION clause are that you can no longer use object editors to modify the encrypted procedure, triggers, or views, and you must maintain the source code of these objects elsewhere.