Introduction to SQL Object Encryption

SQL Server offers a way to encrypt your Stored Procedures to make sure that prying eyes can not see what is going on behind the scenes. The problem with this method is that it is not a very secure way of encrypting the contents of your stored procedures. In addition, since SQL Server basically stores your source code vs. a compiled version most people rely on the code that is in the database server instead of moving the code to a source control application. Because of the need to access this code this tip outlines various methods of decrypting your encrypted database objects.

Basically it is a simple option that you use when creating your objects. Here is a basic example using the "WITH ENCRYPTION" option:

  CREATE PROCEDURE uspGetAuthors
    WITH ENCRYPTION
  AS
    SELECT *
    FROM authors
  GO

After you create this stored procedure, when you try to view the contents of the stored procedure using the following command,

  sp_helptext uspGetAuthors

You will get this error: "The object comments have been encrypted."

Also, when trying to look at this stored procedure in Enterprise Manager You will get the following error message.

AzSQL Decryptor

AzSQL Script Decryptor Overview

AzSQL Script Decryptor is a powerful SQL object decryptor. It allows you unravel a single encrypted sql object or an entire database full of stored procedures, triggers, views and user defined functions with simplicity and blinding speed.