Skip to main content
Last updated on

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

SQL
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.views
  • sys.triggers

Master database

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

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

VIEW DATABASE STATE

Database that you want to ingest

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

SQL
USE <database-name>;
GRANT VIEW DATABASE STATE TO <database-user>;

In SQL Server 2022 and later, you can use the more narrowly scoped VIEW DATABASE PERFORMANCE STATE permission instead:

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

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

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

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:

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

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

VIEW DATABASE STATE

Database that you want to ingest

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

SQL
GRANT VIEW DATABASE STATE TO <database-user>;

In SQL Server 2022 and later, you can use the more narrowly scoped VIEW DATABASE PERFORMANCE STATE permission instead:

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

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

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:

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

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:

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

VIEW DATABASE STATE

Database that you want to ingest

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

SQL
USE <database-name>;
GRANT VIEW DATABASE STATE TO <database-user>;

In SQL Server 2022 and later, you can use the more narrowly scoped VIEW DATABASE PERFORMANCE STATE permission instead:

SQL
USE <database-name>;
GRANT VIEW DATABASE PERFORMANCE STATE TO <database-user>;

Amazon RDS for SQL Server​

On Amazon RDS for SQL Server, master database grants are not required. SQL Server logins automatically receive access to the required system views and stored procedures in master through the public role. You cannot create users in the master database on Amazon RDS.

Required privileges

Where to run the commands

Commands to grant

SELECT privileges on the following system 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:

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;

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

Database that you want to ingest

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

SQL
GRANT SELECT ON SCHEMA::SCHEMA_NAME TO DATABASE_USER;
-- Or for individual tables:
GRANT SELECT ON object::TABLE_NAME 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.

note

For CDC setup on Amazon RDS, the user running the utility setup script must be the RDS master user, or must be granted EXECUTE on msdb.dbo.rds_cdc_enable_db by the master user. You must first create the user in msdb before granting this permission:

SQL
USE msdb;
CREATE USER SETUP_USER FOR LOGIN SETUP_USER;
GRANT EXECUTE ON dbo.rds_cdc_enable_db TO SETUP_USER;
GO

This extra permission is not required for change tracking setup.

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.

On this page