How to script encrypted SQL database objects

One of the ways to keep stored procedure, function or view schema hidden is using the With encryption statements. In this way, access to objects’ DDL script can be locked, so that unauthorized users cannot see them:

CREATE FUNCTION [dbo].[ufnGetAccountingEndDateEnc]()
RETURNS [datetime] 
with encryption 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
GO

The problem is that once an object is encrypted not even sysadmins can see its code. There’s no out of the box solution in SQL Server Management Studio to reverse the encrypting, or decrypting an object, and the most commonly used methods for getting the object’s DDL – fail.

If the Script function option, from the Object Explorer context menu, is used for an encrypted function, the following message will be shown, even when logged as a sysadmin:


SQL Server Management Studio message shown when trying to use the Script function on an encrypted object

The sp_helptext stored procedure which shows the definition of a user-defined rule, default, stored procedure, function, trigger, computed column, Check constraint, view, or system object such as a system stored procedure fails when it comes to encrypted objects:

exec
sp_helptext [ufnGetAccountingEndDateEnc]

Results in:

The text for object ‘ufnGetAccountingEndDateEnc’ is encrypted.

The text and text columns in the syscomments system table are NULL for encrypted objects:

SELECT
* FROM syscomments

SysComments table results for encrypted objects

Even checking out cache doesn’t help, as instead of the DDL in the text column, NULL is shown for encrypted objects:

Text column DDL for encrypted objects

That’s why it’s recommended to keep a copy of the original SQL for the encrypted objects. But, what to do if these copies are not made or a database is inherited with the encrypted objects.

Use DAC connection and query system tables

Use a member of the SQL Server sysadmin role to connect using DAC. Write a query that reads system tables, and get the encrypted object DDL from the imageval column in sys.sysobjvalues table.

The biggest disadvantage of this solution is its complexity.

A simpler solution is to use ApexSQL Script, a SQL Server database migration tool which scripts database objects, including encrypted ones and data into a single or multiple deployment SQL scripts, C# solutions or executable installers.

  1. Start ApexSQL Script

  2. Create a new project

  3. In the New project window, select the SQL Server and provide credentials, if SQL authentication is selected. Note that login must be using a SQL Server sysadmin role. Otherwise, the following message will be shown:

    --Function [dbo].[ufnGetContactInformationEnc] is encrypted.
          It could not be scripted
  4. Click the Connect button

  5. Select a database from the list:

    SQL database selection in the New project window

  6. Click the Load button

  7. The encrypted objects are shown in the Structure view like all other non-encrypted objects:

    DDL script for an encrypted object

  8. Right-click the encrypted stored procedure and select the View DDL for this object command.

    The Create script is shown in a window below:

    Create script for an encrypted object

  9. Close the Integrated editor dialog

  10. Select objects for the scripting process

  11. On the Home tab, click the Script button to create the DDL script for the selected objects:

    Running the Script wizard from the Home tab

  12. In the Scripting mode tab of the Script wizard, select Structure for the Scripting mode and in the Output type step choose the SQL script as the output type:

    Scripting mode step in the Script wizard

    Output type step in the Script wizard

  13. In the Dependencies step, leave the Include dependent objects unchecked, if there is no need to script objects that depend on the objects selected for scripting in the main application window

  14. In the SQL script options step, specify the file path and file name or use default ones:

    SQL script options step in the Script wizard

  15. Click the Create button

  16. After the scripts are created, the following message will be shown:

    Generated SQL script message at the end of the Script wizard

    And the script generated for the encrypted function is:

    /*=============================================================
    SCRIPT HEADER
        
    VERSION:   1.01.0001
    DATE:      01-30-2020 01:36:49
    SERVER:    DESKTOP-EHDJQ3I\SQLEXPRESS
        
    DATABASE: AdventureWorks2012
      Function:  ufnGetAccountingEndDateEnc
    =============================================================*/
        
    SET ARITHABORT ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET ANSI_WARNINGS ON;
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    GO
        
    -- BEGINNING TRANSACTION STRUCTURE
    PRINT 'Beginning transaction STRUCTURE';
    BEGIN TRANSACTION _STRUCTURE_;
    GO
    -- Create Function [dbo].[ufnGetAccountingEndDateEnc]
    PRINT 'Create Function [dbo].[ufnGetAccountingEndDateEnc]';
    GO
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
        
    IF @@ERROR <> 0
    OR @@TRANCOUNT = 0
        BEGIN
            IF @@TRANCOUNT > 0
                BEGIN
                    ROLLBACK
                END;
            SET NOEXEC ON;
        END;
    GO
    CREATE FUNCTION dbo.ufnGetAccountingEndDateEnc(
                                                      )
    RETURNS datetime
        WITH ENCRYPTION
    AS
    BEGIN
        RETURN DATEADD( millisecond, -2, CONVERT( datetime, '20040701', 112
                                                )
                      );
    END;
        
    GO
        
    IF @@ERROR <> 0
    OR @@TRANCOUNT = 0
        BEGIN
            IF @@TRANCOUNT > 0
                BEGIN
                    ROLLBACK
                END;
            SET NOEXEC ON;
        END;
    GO
        
    -- COMMITTING TRANSACTION STRUCTURE
    PRINT 'Committing transaction STRUCTURE';
    IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRANSACTION _STRUCTURE_
        END;
    GO
        
    SET NOEXEC OFF;
    GO

If there are encrypted stored procedures, functions and views, and there are no script files for them, there is no need to use DAC and complex queries for system tables. Use ApexSQL Script to script encrypted objects seamlessly like any other non-encrypted object.

 

April 8, 2013