Microsoft SQL Server database user requirements

Learn which privileges to grant the Microsoft SQL Server database user that you plan to use for ingesting into Azure 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 Where to run the commands Commands to grant
VIEW ANY DATABASE Master database Run the following T-SQL command in the master database:
GRANT VIEW ANY DATABASE TO DATABASE_USER;
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
 Master database Run the following T-SQL commands in the master database:
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;
GRANT SELECT ON object::sys.triggers to DATABASE_USER;
Execute permissions on the following system stored procedures in the master database:
  • sp_tables
  • sp_columns_100
  • sp_pkeys
  • sp_statistics_100
 Master database Run the following T-SQL commands in the database that you want to ingest:
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;
VIEW DATABASE PERFORMANCE STATE Database that you want to ingest Run the following T-SQL command on the database that you want to ingest:
USE DATABASE_NAME
GRANT VIEW DATABASE PERFORMANCE STATE to DATABASE_USER;
SELECT on the schemas and tables that you want to ingest. Database that you want to ingest Run the following T-SQL commands for each schema and table that you want to ingest:
GRANT SELECT ON schema::SCHEMA_NAME to DATABASE_USER;
GRANT SELECT ON object::TABLE_NAME to DATABASE_USER;
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
 Database that you want to ingest Run the following T-SQL commands in 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

Privileges Where to run the commands Commands
Membership in the following server role in the master database so the user can access master:
  • ##MS_DatabaseConnector##
 Master database Run the following T-SQL command in the master database:
ALTER ROLE ##MS_DatabaseConnector## ADD MEMBER DATABASE_USER;
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
 Database that you want to ingest Run the following T-SQL commands on the database that you want to ingest:
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;
VIEW DATABASE PERFORMANCE STATE Database that you want to ingest Run the following T-SQL command on the database that you want to ingest:
GRANT VIEW DATABASE PERFORMANCE STATE TO DATABASE_USER;

Azure SQL Managed Instance

Privileges Where to run the commands 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
 Database that you want to ingest Run the following T-SQL commands on the database that you want to ingest:
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;
View and read access to databases:
  • VIEW ANY DATABASE
  • sys.databases
  • sys.change_tracking_databases
 Master database Run the following T-SQL commands on the master database:
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;
Execute permissions on the following system stored procedures:
  • sp_tables
  • sp_columns_100
  • sp_pkeys
  • sp_statistics_100
 Master database Run the following T-SQL commands on the master database:
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;
VIEW DATABASE PERFORMANCE STATE Database that you want to ingest Run the following T-SQL command on the database that you want to ingest:
USE DATABASE_NAME
GRANT VIEW DATABASE PERFORMANCE STATE TO DATABASE_USER;

Change data capture (CDC) privilege requirements

If CDC is enabled, additional privileges are required on the DDL support objects. See Prepare SQL Server for ingestion using the utility objects script.

Change tracking privilege requirements

If change tracking is enabled, additional privileges are required on the DDL support objects. See Prepare SQL Server for ingestion using the utility objects script.

  • Last updated on