How to script encrypted SQL database objects (4213030)

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 as 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

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, .NET solutions or executable installers.

1. Start ApexSQL Script
2. Create a new project

3. In the Project dialog, 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 Open button

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

   

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

   The CREATE script is shown in a window below:
9. Close the Internal viewer window
10. Select the check boxes left of the 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 choose the TSQL for the Output type:

    
13. In the Dependencies tab, leave the Include dependent database objects unchecked, if there is no need to script objects that depend on the objects selected for scripting in the Main grid

14. In the Output file options tab, under the General tab, select Create and write to file option and specify the file path:

    

15. In the Output file tab, select the Script each object into an individual file option from the Granularity drop-down menu:

    

16. Click the Create button

    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:      02-28-2015 05:03:16
    SERVER:    CRAZY
    
    DATABASE: AdventureWorks2014
      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.