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]
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.
Start ApexSQL Script
Create a new project
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
Click the Connect button
Select a database from the list:
Click the Load button
The encrypted objects are shown in the Structure view like all other non-encrypted objects:
Right-click the encrypted stored procedure and select the View DDL for this object command.
The Create script is shown in a window below:
Close the Integrated editor dialog
Select objects for the scripting process
On the Home tab, click the Script button to create the DDL script for the selected objects:
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:
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
In the SQL script options step, specify the file path and file name or use default ones:
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: 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