SQL Server database user privilege requirements

Preview

LakeFlow Connect 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 database user you plan to use for ingesting SQL Server data into Databricks using LakeFlow Connect.

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

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