SQL Server utility objects script reference

Access reference material for the SQL Server utility objects script, including components, parameters, and troubleshooting.

Overview

The script installs versioned utility stored procedures and functions to set up your SQL Server database for ingestion in Lakeflow Connect. Setup tasks include:

• Permission management
• Change tracking (CT) setup
• Change data capture (CDC) setup
• Platform detection
• DDL support object creation for schema change tracking

Version information

• Current Version: 1.7
• Major Version: 1
• Minor Version: 7
• Version Function: lakeflowUtilityVersion()

What's new in version 1.7

Highlights

• Fixes a data-loss bug in schema evolution. In earlier versions, adding a column to a table that also has a pre-existing (non-Lakeflow) capture instance could ingest the new column as NULL. This is fixed, along with several related schema-evolution failures (see Bug fixes).
• DDL support objects are now optional for change tracking. The DDL audit table and trigger are no longer required for automatic schema evolution and are off by default. Set @CreateDdlSupportingObjects = 1 on lakeflowSetupChangeTracking only if you still want them.
• More reliable schema evolution. Constraint changes are classified so that non-breaking ones, such as foreign keys, no longer trigger unnecessary full refreshes.

Bug fixes

• Fixed several ADD COLUMN schema-evolution failures, including tables that use dynamic data masking, case-sensitive or binary collations, and a reinitialization loop or interrupted ingestion when a pre-existing capture instance is present.
• Fixed collation-conflict errors on databases with a non-default server collation.
• Fixed DDL triggers that could fail when run from a session with non-default SET options.
• Fixed lakeflowFixPermissions to grant the required server-scoped permissions from the master database.
• Fixed uninstall (CLEANUP mode) leaving orphaned DDL triggers that could block ALTER TABLE.

Other changes

• Added @AllowDisablePreExistingCaptureInstances on lakeflowSetupChangeDataCapture. Set it to 1 to let Lakeflow Connect take over and manage a table's pre-existing (non-Lakeflow) CDC capture instance instead of leaving it in place.

Key components

Functions

lakeflowDetectPlatform()

Detects the SQL Server platform type.

Returns: 'AZURE_SQL_DATABASE', 'AZURE_SQL_MANAGED_INSTANCE', 'AMAZON_RDS', 'ON_PREMISES', or 'UNKNOWN'

lakeflowUtilityVersion()

Detects the utility objects version.

Returns: '1.7'

Stored procedures

lakeflowFixPermissions

Grants required permissions to users for ingestion operations.

Parameters:

Parameter	Description
@User (NVARCHAR(128))	Required. Username to grant permissions to
@Tables (NVARCHAR(MAX))	Optional. Controls table-level permission scope

Parameter	Description
@User (NVARCHAR(128))	Required. Username to grant permissions to
@Tables (NVARCHAR(MAX))	Optional. Controls table-level permission scope

@Tables parameter options:

Option	Description
NULL	Grant only system-level permissions (default)
'ALL'	Grant permissions on all user tables in the database
'SCHEMAS:Schema1,Schema2'	Grant permissions on all tables in specified schemas
'Schema.Table1,Schema.Table2'	Grant permissions on specific tables
Wildcard support	Example: 'Sales.*,HR.Employees'

Option	Description
NULL	Grant only system-level permissions (default)
'ALL'	Grant permissions on all user tables in the database
'SCHEMAS:Schema1,Schema2'	Grant permissions on all tables in specified schemas
'Schema.Table1,Schema.Table2'	Grant permissions on specific tables
Wildcard support	Example: 'Sales.*,HR.Employees'

What it does:

• Grants SELECT on required system views (sys.objects, sys.tables, sys.columns, etc.)
• Grants EXECUTE on system stored procedures (sp_tables, sp_columns_100, etc.)
• Optionally grants SELECT on user tables based on the @Tables parameter
• Handles platform-specific differences (Azure SQL Database, Managed Instance, RDS, On-premises)

lakeflowSetupChangeTracking

Enables change tracking at the database and table levels, with optional DDL support (opt-in).

Parameters:

Parameter	Description
@Tables (NVARCHAR(MAX))	Optional. Tables to enable CT on
@User (NVARCHAR(128))	Optional. User to grant permissions to
@Retention (NVARCHAR(50))	Optional. CT retention period (default: '2 DAYS')
@Mode (NVARCHAR(10))	Optional. 'INSTALL' (default) or 'CLEANUP'
@CreateDdlSupportingObjects (BIT)	Optional. Default 0. Set to 1 to create the DDL audit table and trigger for schema-change (DDL) tracking. Setup validation expects this value to match your pipeline configuration. If the values do not match, setup validation reports a failure.

Parameter	Description
@Tables (NVARCHAR(MAX))	Optional. Tables to enable CT on
@User (NVARCHAR(128))	Optional. User to grant permissions to
@Retention (NVARCHAR(50))	Optional. CT retention period (default: '2 DAYS')
@Mode (NVARCHAR(10))	Optional. 'INSTALL' (default) or 'CLEANUP'
@CreateDdlSupportingObjects (BIT)	Optional. Default 0. Set to 1 to create the DDL audit table and trigger for schema-change (DDL) tracking. Setup validation expects this value to match your pipeline configuration. If the values do not match, setup validation reports a failure.

@Tables parameter options:

Option	Description
NULL	Set up database-level CT only, no table enablement (also creates DDL support objects when @CreateDdlSupportingObjects = 1)
'ALL'	Enable CT on all user tables with primary keys
'SCHEMAS:Schema1,Schema2'	Enable CT on tables in specified schemas
'Schema.Table1,Schema.Table2'	Enable CT on specific tables
Wildcard support	Example: 'Sales.*,HR.Employees'

Option	Description
NULL	Set up database-level CT only, no table enablement (also creates DDL support objects when @CreateDdlSupportingObjects = 1)
'ALL'	Enable CT on all user tables with primary keys
'SCHEMAS:Schema1,Schema2'	Enable CT on tables in specified schemas
'Schema.Table1,Schema.Table2'	Enable CT on specific tables
Wildcard support	Example: 'Sales.*,HR.Employees'

What it does:

• Enables change tracking at the database level if not already enabled
• When @CreateDdlSupportingObjects = 1, creates a versioned DDL audit table (lakeflowDdlAudit_1_7) and a trigger to capture schema changes
• Enables CT on specified tables (skips tables without primary keys)
• Grants VIEW CHANGE TRACKING permissions to the specified user
• CLEANUP mode: Removes DDL support objects

Important behaviors:

• Automatically skips tables without primary keys (CDC is recommended for these)
• Smart discovery with the 'ALL' parameter
• Idempotent: Safe to run multiple times

lakeflowSetupChangeDataCapture

Enables CDC at the database and table levels with DDL support and capture instance management.

Parameters:

Parameter	Description
@Tables (NVARCHAR(MAX))	Optional. Tables to enable CDC on
@User (NVARCHAR(128))	Optional. User to grant permissions to
@Mode (NVARCHAR(10))	Optional. 'INSTALL' (default) or 'CLEANUP'
@AllowDisablePreExistingCaptureInstances (BIT)	Optional. 0 (default) leaves pre-existing, non-Lakeflow capture instances untouched during schema change handling. Set to 1 to let Lakeflow Connect take ownership of and disable pre-existing capture instances.

Parameter	Description
@Tables (NVARCHAR(MAX))	Optional. Tables to enable CDC on
@User (NVARCHAR(128))	Optional. User to grant permissions to
@Mode (NVARCHAR(10))	Optional. 'INSTALL' (default) or 'CLEANUP'
@AllowDisablePreExistingCaptureInstances (BIT)	Optional. 0 (default) leaves pre-existing, non-Lakeflow capture instances untouched during schema change handling. Set to 1 to let Lakeflow Connect take ownership of and disable pre-existing capture instances.

@Tables parameter options:

Option	Description
NULL	Set up database-level CDC and DDL support only
'ALL'	Enable CDC on all user tables
'SCHEMAS:Schema1,Schema2'	Enable CDC on tables in specified schemas
'Schema.Table1,Schema.Table2'	Enable CDC on specific tables

Option	Description
NULL	Set up database-level CDC and DDL support only
'ALL'	Enable CDC on all user tables
'SCHEMAS:Schema1,Schema2'	Enable CDC on tables in specified schemas
'Schema.Table1,Schema.Table2'	Enable CDC on specific tables

What it does:

• Enables CDC at the database level if not already enabled
• Creates a capture instance tracking table (lakeflowCaptureInstanceInfo_1_7)
• Creates helper procedures for capture instance management:
  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7
• Creates an ALTER TABLE trigger for automatic schema change handling
• Enables CDC on specified tables
• Grants required CDC permissions to the specified user
• CLEANUP mode: Removes all CDC DDL support objects

Important behaviors:

• Works with tables with or without primary keys
• Automatically handles capture instance rotation on schema changes
• Idempotent: Safe to run multiple times

Platform support

• On-premises SQL Server (EngineEdition 1-4)
• Azure SQL Database (EngineEdition 5)
• Azure SQL Managed Instance (EngineEdition 8)
• Amazon RDS for SQL Server (detected by server name pattern)

Prerequisites

• User executing the script must be a member of db_owner role
• For CT setup: Change tracking must be available on the platform
• For CDC setup: Change data capture must be available on the platform

Installation instructions

Download and run the script

1. Download the latest version of the script:

   Download utility_script.sql

2. Run the script:

   1. Open the downloaded script in SQL Server Management Studio (SSMS), Azure Data Studio, or your preferred SQL client.
   2. Connect to your SQL Server instance.
   3. Confirm that you are connected to the target database where you want to install the utility objects.
   4. Run the script.

3. Verify installation:

   SQL

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;

Alternative: Run using the command line

If you prefer to use sqlcmd:

Bash

sqlcmd -S YourServerName -d YourDatabase -E -i utility_script.sql

note

Replace YourServerName and YourDatabase with your actual server and database names. Use -U username -P password instead of -E if not using Windows authentication.

Example: Fix permissions (system only)

SQL

-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser';

Example: Fix permissions (with table access)

SQL

-- Grant system permissions plus access to all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'ALL';

-- Grant permissions for specific schemas
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'SCHEMAS:Sales,HR,Production';

-- Grant permissions for specific tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'Sales.Orders,HR.Employees';

Examples: Change tracking setup

Database-level only

SQL

-- Setup CT infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = NULL,
    @User = 'myuser';

Enable on all tables

SQL

-- Enable CT on all user tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

Schema-based setup

SQL

-- Enable CT on all tables in specific schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'myuser',
    @Retention = '3 DAYS';

Specific tables

SQL

-- Enable CT on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'dbo.Table1,Sales.Orders,HR.Employees',
    @User = 'myuser';

Create DDL support objects

SQL

-- Enable CT and create the DDL audit table and trigger for schema-change capture
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser',
    @CreateDdlSupportingObjects = 1;

Examples: CDC setup

Database-level only

SQL

-- Setup CDC infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = NULL,
    @User = 'myuser';

Enable on all tables

SQL

-- Enable CDC on all user tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Specific tables

SQL

-- Enable CDC on specific tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'dbo.Table1,Sales.Orders',
    @User = 'myuser';

Take ownership of pre-existing capture instances

SQL

-- Allow Lakeflow to disable pre-existing (non-Lakeflow) capture instances
-- during schema change handling
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser',
    @AllowDisablePreExistingCaptureInstances = 1;

Example: Hybrid approach

SQL

-- Step 1: Enable CT on tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

-- Step 2: Enable CDC on remaining tables (without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Example: Cleanup

SQL

-- Remove CT DDL support objects
EXEC dbo.lakeflowSetupChangeTracking
    @Mode = 'CLEANUP';

-- Remove CDC DDL support objects
EXEC dbo.lakeflowSetupChangeDataCapture
    @Mode = 'CLEANUP';

DDL support objects created

The following DDL support objects are created, depending on whether you use change tracking or CDC.

For change tracking

Object type	Name	Description
Table	lakeflowDdlAudit_1_7	Stores DDL change history
Trigger	lakeflowDdlAuditTrigger_1_7	Captures ALTER TABLE events

Object type	Name	Description
Table	lakeflowDdlAudit_1_7	Stores DDL change history
Trigger	lakeflowDdlAuditTrigger_1_7	Captures ALTER TABLE events

For CDC

Object type	Name	Description
Table	lakeflowCaptureInstanceInfo_1_7	Tracks capture instances
Procedure	lakeflowDisableOldCaptureInstance_1_7	Removes old capture instance
Procedure	lakeflowMergeCaptureInstances_1_7	Merges data between instances
Procedure	lakeflowRefreshCaptureInstance_1_7	Creates new capture instance
Trigger	lakeflowAlterTableTrigger_1_7	Handles schema changes

Object type	Name	Description
Table	lakeflowCaptureInstanceInfo_1_7	Tracks capture instances
Procedure	lakeflowDisableOldCaptureInstance_1_7	Removes old capture instance
Procedure	lakeflowMergeCaptureInstances_1_7	Merges data between instances
Procedure	lakeflowRefreshCaptureInstance_1_7	Creates new capture instance
Trigger	lakeflowAlterTableTrigger_1_7	Handles schema changes

Change tracking limitations

• Requires primary keys: Tables without primary keys cannot use change tracking.
• The script automatically skips tables without PKs and recommends using CDC instead.

Platform-specific behavior

• Azure SQL Database: System stored procedures are accessible by default (no EXECUTE grants needed).
• Server-scoped views: Limited access in Azure SQL Database for views like sys.change_tracking_databases.

Upgrade path

To upgrade, re-run the utility objects script, then re-run the setup procedures. The script recreates the utility functions and removes only legacy replicant-prefixed objects from earlier releases. It leaves your existing Lakeflow DDL support objects in place, so the current DDL trigger keeps capturing schema changes until you re-run the setup procedures. For instructions, see Step 1: Install or upgrade utility objects.

What happens during an upgrade:

• The utility functions and setup procedures defined by the script are dropped and recreated.
• Legacy replicant-prefixed objects from earlier releases are removed.
• Existing Lakeflow DDL support objects (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_*, and related procedures and triggers) are left in place. Re-running the setup procedures transitions them to the new version. The capture instance objects move whenever you re-run lakeflowSetupChangeDataCapture. The DDL audit table and trigger move only when you re-run lakeflowSetupChangeTracking with @CreateDdlSupportingObjects = 1, because they are opt-in.
• Change tracking and CDC remain enabled on your tables. The upgrade does not disable them.
• CDC capture instances are not affected by the upgrade script.

After you run the upgrade script, re-run the setup procedures to recreate DDL support objects for the new version:

• lakeflowSetupChangeTracking: with @CreateDdlSupportingObjects = 1, carries the DDL audit table and trigger forward to lakeflowDdlAudit_1_7. Pass this flag if your install uses DDL schema-change capture (it has a lakeflowDdlAudit table), or the objects stay on the old version.
• lakeflowSetupChangeDataCapture: Recreates lakeflowCaptureInstanceInfo_1_7 and the related procedures and trigger.

Both procedures are idempotent and safe to re-run with the original setup parameters.

note

To revert to a previous version of the utility objects script, contact Databricks Support.

Versioning scheme: objectName_majorVersion_minorVersion. Current objects use the _1_7 suffix.

Best practices

• Always run as db_owner or a user with equivalent privileges.
• Test on non-production databases first.
• Use the hybrid approach for comprehensive coverage.
• Run lakeflowFixPermissions after setup to ensure proper user access.
• Consider retention periods based on your ingestion frequency.

Troubleshooting

"User executing this script is not a 'db_owner' role member"

Solution: Execute as a user with db_owner role

"Change tracking is not enabled on catalog"

Solution: Enable CT at the database level or let the procedure handle it automatically

"Change data capture is not enabled on catalog"

Solution: Enable CDC at the database level or let the procedure handle it automatically

"Tables skipped due to missing primary keys"

Solution: Use lakeflowSetupChangeDataCapture for these tables instead

Validation integration

The following utility objects are validated by the Java validation framework:

Object	Description
SqlServerUtilityObjectsSetupValidator	Validates utility objects installation
SqlServerChangeDataManagementSetupValidator	Validates CT/CDC setup
SqlServerDdlSupportObjectsSetupValidator	Validates DDL support objects
SqlServerPermissionsSetupValidator	Validates permissions

Object	Description
SqlServerUtilityObjectsSetupValidator	Validates utility objects installation
SqlServerChangeDataManagementSetupValidator	Validates CT/CDC setup
SqlServerDdlSupportObjectsSetupValidator	Validates DDL support objects
SqlServerPermissionsSetupValidator	Validates permissions

Migration notes

If upgrading from older versions of DDL support objects (pre-utility objects era):

• The script automatically cleans up legacy objects.
• No manual cleanup is required.
• Version 1.1 consolidates all functionality into unified procedures.

Additional resources

• Prepare SQL Server for ingestion using the utility objects script
• Configure Microsoft SQL Server for ingestion into Databricks
• Microsoft SQL Server database user requirements
• Track data changes (SQL Server) in the SQL Server documentation
• About Change Tracking (SQL Server) in the SQL Server documentation
• What is change data capture (CDC)? in the SQL Server documentation