Skip to main content

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.1
  • Major Version: 1
  • Minor Version: 1
  • Version Function: lakeflowUtilityVersion_1_1()

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_1_1()​

Detects the utility objects version.

Returns: '1.1'

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

@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'

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 DDL support.

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'

@Tables parameter options:

Option

Description

NULL

Setup database-level CT and DDL support only (no table enablement)

'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
  • Creates a versioned DDL audit table (lakeflowDdlAudit_1_2)
  • Creates a DDL audit 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'

@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

What it does:

  • Enables CDC at the database level if not already enabled
  • Creates a capture instance tracking table (lakeflowCaptureInstanceInfo_1_2)
  • Creates helper procedures for capture instance management:
    • lakeflowDisableOldCaptureInstance_1_2
    • lakeflowMergeCaptureInstances_1_2
    • lakeflowRefreshCaptureInstance_1_2
  • 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 script

    Download lakeflow_utility_objects.sql from [URL TO BE DETERMINED]

  2. Run the script

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

  3. Verify installation

    SQL
    -- Verify installation
    SELECT dbo.lakeflowUtilityVersion_1_1() 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 lakeflow_utility_objects.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';

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';

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_2

Stores DDL change history

Trigger

lakeflowDdlAuditTrigger_1_2

Captures ALTER TABLE events

For CDC​

Object type

Name

Description

Table

lakeflowCaptureInstanceInfo_1_2

Tracks capture instances

Procedure

lakeflowDisableOldCaptureInstance_1_2

Removes old capture instance

Procedure

lakeflowMergeCaptureInstances_1_2

Merges data between instances

Procedure

lakeflowRefreshCaptureInstance_1_2

Creates new capture instance

Trigger

lakeflowAlterTableTrigger_1_2

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​

  • The script automatically drops all previous versions when run.
  • Versioning scheme: objectName_majorVersion_minorVersion
  • Current objects use _1_1 suffix for version tracking.

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

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​

Last updated on