Skip to main content

Microsoft SQL Server database user requirements

Preview

The Microsoft SQL Server connector is in Public Preview.

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

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

Grant database privileges​

The tables in this section list the privileges that the database user must have, depending on the database variation. These privileges are required whether you use change data capture (CDC) or change tracking. The tables also show the commands to grant each privilege.

Non-Azure databases​

Required privileges

Commands to grant

Where to run the commands

Read access to the following system tables and views in 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

Run the following T-SQL commands in the master database:

SQL
GRANT SELECT ON object::sys.databases to <database-user>;
GRANT SELECT ON object::sys.schemas to <database-user>;
GRANT SELECT ON object::sys.tables to <database-user>;
GRANT SELECT ON object::sys.columns to <database-user>;
GRANT SELECT ON object::sys.key_constraints to <database-user>;
GRANT SELECT ON object::sys.foreign_keys to <database-user>;
GRANT SELECT ON object::sys.check_constraints to <database-user>;
GRANT SELECT ON object::sys.default_constraints to <database-user>;
GRANT SELECT ON object::sys.change_tracking_tables to <database-user>;
GRANT SELECT ON object::sys.change_tracking_databases to <database-user>;
GRANT SELECT ON object::sys.objects to <database-user>;
GRANT SELECT ON object::sys.views to <database-user>;

Master database

Execute permissions on the following system stored procedures in the master database:

  • sp_tables
  • sp_columns_100
  • sp_pkeys
  • sp_statistics_100

Run the following T-SQL commands in the database that you want to ingest:

SQL
GRANT EXECUTE ON object::sp_tables to <database-user>;
GRANT EXECUTE ON object::sp_columns_100 to <database-user>;
GRANT EXECUTE ON object::sp_pkeys to <database-user>;
GRANT EXECUTE ON object::sp_statistics_100 to <database-user>;

Master database

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

Run the following T-SQL command for each schema and table that you want to ingest:

SQL
GRANT SELECT ON object::<table-name|schema-name> to <database-user>;

Database that you want to ingest

SELECT privileges on the following system tables and views in the database that you want to ingest:

  • sys.indexes
  • sys.index_columns
  • sys.columns
  • sys.tables
  • sys.fulltext_index_columns
  • sys.fulltext_indexes

Run the following T-SQL commands in the database that you want to ingest:

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

Database that you want to ingest

Azure SQL Database​

Privileges

Commands

Where to run the commands

Read access to the following system tables and views in the database that you want to ingest:

  • sys.schemas
  • sys.tables
  • sys.columns
  • sys.key_constraints
  • sys.foreign_keys
  • sys.check_constraints
  • sys.default_constraints
  • sys.change_tracking_tables
  • sys.objects
  • sys.triggers
  • sys.indexes
  • sys.index_columns
  • sys.fulltext_index_columns
  • sys.fulltext_indexes

Run the following T-SQL commands on the database that you want to ingest:

SQL
GRANT SELECT ON object::sys.schemas TO <database-user>;
GRANT SELECT ON object::sys.tables TO <database-user>;
GRANT SELECT ON object::sys.columns TO <database-user>;
GRANT SELECT ON object::sys.key_constraints TO <database-user>;
GRANT SELECT ON object::sys.foreign_keys TO <database-user>;
GRANT SELECT ON object::sys.check_constraints TO <database-user>;
GRANT SELECT ON object::sys.default_constraints TO <database-user>;
GRANT SELECT ON object::sys.change_tracking_tables TO <database-user>;
GRANT SELECT ON object::sys.objects TO <database-user>;
GRANT SELECT ON object::sys.triggers TO <database-user>;
GRANT SELECT ON object::sys.indexes TO <database-user>;
GRANT SELECT ON object::sys.index_columns TO <database-user>;
GRANT SELECT ON object::sys.fulltext_index_columns TO <database-user>;
GRANT SELECT ON object::sys.fulltext_indexes TO <database-user>;
GRANT SELECT ON schema::<schema-name> TO <database-user>;
GRANT SELECT ON object::<table-name> TO <database-user>;

Database that you want to ingest

Azure SQL Managed Instance​

Privileges

Commands

Where to run the commands

Read access to the following system tables and views in the database that you want to ingest:

  • sys.schemas
  • sys.tables
  • sys.columns
  • sys.key_constraints
  • sys.foreign_keys
  • sys.check_constraints
  • sys.default_constraints
  • sys.change_tracking_tables
  • sys.objects
  • sys.triggers
  • sys.indexes
  • sys.index_columns
  • sys.fulltext_index_columns
  • sys.fulltext_indexes

Run the following T-SQL commands on the database that you want to ingest:

SQL
GRANT SELECT ON object::sys.schemas TO <database-user>;
GRANT SELECT ON object::sys.tables TO <database-user>;
GRANT SELECT ON object::sys.columns TO <database-user>;
GRANT SELECT ON object::sys.key_constraints TO <database-user>;
GRANT SELECT ON object::sys.foreign_keys TO <database-user>;
GRANT SELECT ON object::sys.check_constraints TO <database-user>;
GRANT SELECT ON object::sys.default_constraints TO <database-user>;
GRANT SELECT ON object::sys.change_tracking_tables TO <database-user>;
GRANT SELECT ON object::sys.objects TO <database-user>;
GRANT SELECT ON object::sys.triggers TO <database-user>;
GRANT SELECT ON object::sys.indexes TO <database-user>;
GRANT SELECT ON object::sys.index_columns TO <database-user>;
GRANT SELECT ON object::sys.fulltext_index_columns TO <database-user>;
GRANT SELECT ON object::sys.fulltext_indexes TO <database-user>;
GRANT SELECT ON schema::<schema-name> TO <database-user>;
GRANT SELECT ON object::<table-name> TO <database-user>;

Database that you want to ingest

View and read access to databases:

  • VIEW ANY DATABASE
  • sys.databases
  • sys.change_tracking_databases

Run the following T-SQL commands on the master database:

SQL
GRANT VIEW ANY DATABASE TO <database-user>;
GRANT SELECT ON sys.databases TO <database-user>;
GRANT SELECT ON object::sys.change_tracking_databases TO <database-user>;

Master database

Execute permissions on the following system stored procedures:

  • sp_tables
  • sp_columns_100
  • sp_pkeys
  • sp_statistics_100

Run the following T-SQL commands on the master database:

SQL
GRANT EXECUTE ON object::sp_tables TO <database-user>;
GRANT EXECUTE ON object::sp_columns_100 TO <database-user>;
GRANT EXECUTE ON object::sp_pkeys TO <database-user>;
GRANT EXECUTE ON object::sp_statistics_100 TO <database-user>;

Master database

Change data capture (CDC) privilege requirements​

If CDC is enabled, additional privileges are required on the DDL support objects. See Enable built-in CDC in Microsoft SQL Server.

Change tracking privilege requirements​

If change tracking is enabled, additional privileges are required on the DDL support objects. See Enable change tracking in Microsoft SQL Server.

Last updated on