If you’re concerned about unauthorized users looking at the business logic implemented in SQL stored procs (e.g. deploying database to client computers or using a shared SQL server), this blog is for you.

 

SQL Server can encrypt the following components: definitions of stored procedures, views, triggers, user-defined functions, defaults, rules, as well as the data sent between the server and the client.

 

Encrypting a stored procedure is as simple as using the WITH option:

CREATE PROCEDURE ...

WITH ENCRYPTION AS…

 

As stated in documentation, WITH ENCRYPTION option indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

 

For information on encrypting data transmitted across a network between an instance of SQL Server and a client application, check out http://msdn2.microsoft.com/en-us/library/ms189067.aspx.