SQL Server database user privilege requirements

Preview

The Microsoft SQL Server connector is in gated Public Preview. To participate in the preview, contact your Databricks account team.

This article describes the privileges you must grant to the SQL Server database user you plan to use for ingesting into Databricks using Lakeflow Connect.

Databricks recommends that you create a database user that is solely used for Databricks ingestion.

General privilege requirements

This database user must have the following privileges regardless of the method employed for tracking data changes in the source database:

• Read access to the following system tables and views:

  • sys.databases
  • sys.schemas
  • sys.tables
  • sys.columns
  • sys.key_constraints
  • sys.foreign_keys
  • sys.check_constraints
  • sys.default_constraints
  • sys.change_tracking_tables
  • sys.change_tracking_databases
  • sys.objects
  • sys.triggers

• Execute permissions on the following system stored procedures:

  • sp_tables
  • sp_columns
  • sp_columns_100
  • sp_pkeys
  • sp_statistics

• SELECT on the schemas and tables you want to ingest.

• The following SELECT privileges:

  use <database-name>
  
  grant select on object::sys.indexes to <cdc-username>;
  grant select on object::sys.index_columns to <cdc-username>;
  grant select on object::sys.columns to <cdc-username>;
  grant select on object::sys.tables to <cdc-username>;
  
  grant select on object::sys.fulltext_index_columns to <cdc-username>;
  grant select on object::sys.fulltext_indexes to <cdc-username>;

Change data capture (CDC) privilege requirements

note

If you set the replicationUser variable in the DDL support objects script when you enable CDC, the script grants these privileges for you. You only need to manually grant these if replicationUser is unset in the script.

For change data capture (CDC), the following permissions are also required:

GRANT VIEW DEFINITION ON object::dbo.lakeflowDisableOldCaptureInstance_1_1 TO <database-user>;
GRANT VIEW DEFINITION ON object::dbo.lakeflowRefreshCaptureInstance_1_1 TO <database-user>;
GRANT VIEW DEFINITION ON object::dbo.lakeflowMergeCaptureInstances_1_1 TO <database-user>;
GRANT VIEW DEFINITION TO <database-user>;
GRANT VIEW DATABASE PERFORMANCE STATE TO <database-user>;
GRANT UPDATE ON object::dbo.lakeflowCaptureInstanceInfo_1_1 TO <database-user>;
GRANT EXECUTE ON schema :: dbo TO <database-user>;
GRANT EXECUTE ON object::dbo.lakeflowMergeCaptureInstances_1_1 TO <database-user>;
GRANT EXECUTE ON object::dbo.lakeflowDisableOldCaptureInstance_1_1 TO <database-user>;
GRANT EXECUTE ON object::dbo.lakeflowRefreshCaptureInstance_1_1 TO <database-user>;

Change tracking privilege requirements

note

If you set the replicationUser variable in the DDL support objects script when you enable change tracking, the script grants these privileges for you. You only need to manually grant these if replicationUser is unset in the script.

For change tracking, the following permissions are also required:

GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.lakeflowDdlAudit_1_1 TO <database-user>;
GRANT VIEW DEFINITION ON DATABASE::<database-name> TO <database-user>;