Skip to main content

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:

Privileges

Read access to the following system tables and views on the master database:

  • 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 on the database that you want to ingest:

  • sp_tables
  • sp_columns_100
  • sp_pkeys
  • sp_statistics_100

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

The following SELECT privileges on the database that you want to ingest:

use <database-name>

grant select on object::sys.indexes to <database-user>;
grant select on object::sys.index_columns to <database-user>;
grant select on object::sys.columns to <database-user>;
grant select on object::sys.tables to <database-user>;

grant select on object::sys.fulltext_index_columns to <database-user>;
grant select on object::sys.fulltext_indexes to <database-user>;

Azure SQL Database access is managed differently. To grant a user access to a database, assign the following roles:

  • db_datareader: Allows the user to read data from all user tables.
  • db_datawriter: Allows the user to add, delete, or modify data in all user tables.
  • db_ddladmin: Allows the user to run DDL commands in the database.

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>;
Last updated on