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:

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

Even checking out cache doesn’t help, as instead of the DDL in the text column, NULL is shown 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:

   

6. Click the Load button

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

   

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:

   

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:

    

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:

    

    

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:

    

15. Click the Create button

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

    

    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.