Permissions
Each available Feature Set is supported by a corresponding set of SQL Server types. For details on the individual permissions that must be granted to the extension user for each Feature Set, please refer to the Involved Views and Tables section and the granular permission details for each system view provided below.
Supported systems and involved system views per feature set
default
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Views and tables involved:
- sys.dm_os_sys_info
- sys.dm_os_performance_counters
- sys.databases
Memory
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.dm_os_performance_counters
Locks
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.dm_os_performance_counters
Latches
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.dm_os_performance_counters
Queries
-
Monitoring query performance stats
Supported on:
- SQL Server (all versions)
Involved Views and Tables:
- sys.dm_os_performance_counters
-
Monitoring TOP longest queries:
Supported on:
- SQL Server (2016 and later)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
Involved Views and Tables:
- sys.query_store_runtime_stats
- sys.query_store_plan
- sys.query_store_query
- sys.query_store_query_text
Replication
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.dm_os_performance_counters
Sessions
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
- SQL analytics endpoint in Microsoft Fabric
- Warehouse in Microsoft Fabric
Involved Views and Tables:
Transaction logs
Supported on:
- SQL Server (all versions)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.dm_os_performance_counters
Backups
-
Monitoring backup sets per database
Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
- Analytics Platform System (PDW)
Involved Views and Tables:
- sys.databases
- msdb.dbo.backupset
-
Monitoring backup files per database
Supported on:
- SQL Server (all versions)
Involved Views and Tables:
- sys.databases
- msdb.dbo.backupset
- msdb.dbo.backupfile
Database files
-
Monitoring database files stats
Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
- Analytics Platform System (PDW)
Involved Views and Tables:
-
Monitoring largest database files on Azure SQL Database
Supported on:
Involved Views and Tables:
-
Monitoring largest database files on other SQL Server types
Supported on:
- SQL Server (all versions)
- Azure SQL Managed Instance
- Analytics Platform System (PDW)
Involved Views and Tables:
Always On
Supported on:
- SQL Server (2016 and later)
Involved Views and Tables:
- sys.availability_groups
- sys.availability_replicas
- sys.availability_databases_cluster
- sys.dm_hadr_availability_group_states
- sys.dm_hadr_availability_replica_states
- sys.dm_hadr_database_replica_states
Important:
To obtain information about every replica in a given availability group, connect the extension to the server instance that is hosting the primary replica. When connected to a server instance that is hosting a secondary replica of an availability group, the extension returns only local information for the availability group.
When connected to a secondary replica, the extension retrieves states of every secondary database on the server instance. On the primary replica, the extension returns data for each primary database and for the corresponding secondary database.
Depending on the action and higher-level states, database-state information may be unavailable or out of date. Furthermore, the values have only local relevance. See limitations of sys.dm_hadr_database_replica_states
.
Notes:
When a database is added to an availability group, the primary database is automatically joined to the group. Secondary databases must be manually prepared on each secondary replica before they can be joined to the availability group.
If the local server instance cannot communicate with the WSFC failover cluster, for example, because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. These rows will contain only the columns of data that are cached locally in metadata.
Jobs
Supported on:
- SQL Server (all versions)
Involved Views and Tables
- msdb.dbo.sysjobs
- msdb.dbo.sysjobsteps
- msdb.dbo.sysjobhistory
- msdb.dbo.sysjobservers
- msdb.dbo.sysjobactivity
Required permissions
Specific permissions required per system view
sys.dm_os_sys_info
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.
- Azure SQL Database (Basic, S0, S1 service objectives and for databases in elastic pools)
- Azure SQL Database (All other service objectives)
VIEW DATABASE STATE
permission on the database; or
##MS_ServerStateReader##
server role.
- Azure SQL Managed Instance
VIEW SERVER STATE
permission.
sys.dm_os_performance_counters
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.
- Azure SQL Database (Basic, S0, S1 service objectives and for databases in elastic pools)
- Azure SQL Database (All other service objectives)
VIEW DATABASE STATE
permission on the database; or
##MS_ServerStateReader##
server role.
- Azure SQL Managed Instance
VIEW SERVER STATE
permission.
sys.databases
- Azure SQL Database
- Connect to
master
database for all databases to be visible.
- When connecting to a user database, only the current database and the
master
database are visible.
- Other supported types of SQL Server
- To see just the database extension is connected to:
- No additional permissions are required.
- To see all
ONLINE
databases:
VIEW ANY DATABASE
(default permission for the public role)
- To see all
OFFLINE
databases as well:
ALTER ANY DATABASE
on server level; or
CREATE DATABASE
permission in the master
database.
sys.query_store_runtime_stats
- All supported types of SQL Server
VIEW DATABASE STATE
permission.
sys.query_store_plan
- All supported types of SQL Server
VIEW DATABASE STATE
permission.
sys.query_store_query
- All supported types of SQL Server
VIEW DATABASE STATE
permission.
sys.query_store_query_text
- All supported types of SQL Server
VIEW DATABASE STATE
permission.
sys.dm_exec_sessions
- To see the sessions of the user extension connects with:
- No additional permissions are required.
- To see all sessions within the database extension is connected to:
VIEW DATABASE STATE
permission.
- To see all sessions on the server:
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.
msdb.dbo.backupset
- Available as read-only to any user with public level access to the instance.
msdb.dbo.backupfile
- Available as read-only to any user with public level access to the instance.
sys.master_files
- All supported types of SQL Server:
VIEW ANY DEFINITION
; or
CREATE DATABASE
; or
ALTER ANY DATABASE
.
sys.database_files
- All supported types of SQL Server:
sys.availability_groups
- All supported types of SQL Server:
VIEW ANY DEFINITION
permission.
sys.availability_replicas
- All supported types of SQL Server:
VIEW ANY DEFINITION
permission.
sys.availability_databases_cluster
- All supported types of SQL Server:
- If the user with which extension makes the calls is the owner of the database, no additional permissions are required.
- Otherwise:
VIEW ANY DATABASE
; or
ALTER ANY DATABASE
; or
CREATE DATABASE
permission in master
is required.
sys.dm_hadr_availability_group_states
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.
sys.dm_hadr_availability_replica_states
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.
sys.dm_hadr_database_replica_states
- SQL Server (2022 and later)
VIEW SERVER PERFORMANCE STATE
permission.
- SQL Server (up to 2019)
VIEW SERVER STATE
permission.