แก้ไข

แชร์ผ่าน


Roles created by Azure extension for SQL Server installation

Applies to: SQL Server

This article lists the server and database roles and mappings that the installation of Azure extension for SQL Server creates.

Roles

When you install Azure extension for SQL Server, the installation:

  1. Creates a server level role: SQLArcExtensionServerRole

  2. Creates a database level role: SQLArcExtensionUserRole

  3. Adds NT AUTHORITY\SYSTEM* account to each role

  4. Maps NT AUTHORITY\SYSTEM* at the database level for each database

  5. Grants minimum permissions for the enabled features

    *Alternatively, you can configure SQL Server enabled by Azure Arc to run in least privilege mode (available in preview). For details, review Operate SQL Server enabled by Azure Arc with least privilege (preview).

In addition, Azure extension for SQL Server revokes permissions for these roles when they're no longer needed for specific features.

SqlServerExtensionPermissionProvider is a Windows task. It grants or revokes privileges in SQL Server when it detects:

  • A new SQL Server instance is installed on the host
  • SQL Server instance is uninstalled from host
  • An instance level feature is enabled or disabled or settings are updated
  • Extension service is restarted

Note

Prior to the July, 2024 release, SqlServerExtensionPermissionProvider is a scheduled task. It runs hourly.

For details, review Configure Windows service accounts and permissions for Azure extension for SQL Server.

If you uninstall Azure extension for SQL Server, the server and database level roles are removed.

Permissions

Feature Permission Level Role
Default VIEW SERVER STATE Server Level SQLArcExtensionServerRole
CONNECT SQL Server Level SQLArcExtensionServerRole
VIEW ANY DEFINITION Server Level SQLArcExtensionServerRole
VIEW ANY DATABASE Server Level SQLArcExtensionServerRole
CONNECT ANY DATABASE Server Level SQLArcExtensionServerRole
SELECT dbo.sysjobactivity msdb SQLArcExtensionUserRole
SELECT dbo.sysjobs msdb SQLArcExtensionUserRole
SELECT dbo.syssessions msdb SQLArcExtensionUserRole
SELECT dbo.sysjobHistory msdb SQLArcExtensionUserRole
SELECT dbo.sysjobSteps msdb SQLArcExtensionUserRole
SELECT dbo.syscategories msdb SQLArcExtensionUserRole
SELECT dbo.sysoperators msdb SQLArcExtensionUserRole
SELECT dbo.suspectpages msdb SQLArcExtensionUserRole
SELECT dbo.backupset msdb SQLArcExtensionUserRole
SELECT dbo.backupmediaset msdb SQLArcExtensionUserRole
SELECT dbo.backupmediafamily msdb SQLArcExtensionUserRole
SELECT dbo.backupfile msdb SQLArcExtensionUserRole
Backup CREATE ANY DATABASE Server Level SQLArcExtensionServerRole
db_backupoperator role All databases SQLArcExtensionUserRole
dbcreator Server Level SQLArcExtensionServerRole
Azure Control Plane CREATE TABLE msdb SQLArcExtensionUserRole
ALTER ANY SCHEMA msdb SQLArcExtensionUserRole
CREATE TYPE msdb SQLArcExtensionUserRole
EXECUTE msdb SQLArcExtensionUserRole
db_datawriter role msdb SQLArcExtensionUserRole
db_datareader role msdb SQLArcExtensionUserRole
Availability Group Discovery VIEW ANY DEFINITION Server Level SQLArcExtensionServerRole
Purview SELECT All databases SQLArcExtensionUserRole
EXECUTE All databases SQLArcExtensionUserRole
Migration Assessment EXECUTE dbo.agent_datetime msdb SQLArcExtensionUserRole
SELECT dbo.sysjobs msdb SQLArcExtensionUserRole
SELECT dbo.sysmail_account msdb SQLArcExtensionUserRole
SELECT dbo.sysmail_profile msdb SQLArcExtensionUserRole
SELECT dbo.sysmail_profileaccount msdb SQLArcExtensionUserRole
SELECT dbo.syssubsystems msdb SQLArcExtensionUserRole
SELECT sys.sql_expression_dependencies All databases SQLArcExtensionUserRole

Run with least privilege

To run Azure extension for SQL Server with least privilege, follow the instructions at Operate SQL Server enabled by Azure Arc with least privilege.

At this time, the least privilege configuration is not the default.

Configure Windows service accounts and permissions