Azure SQL Collection

When collecting data from Azure Managed Instances or Azure SQL Databases, host-level metrics are not available. This is expected behavior for Platform as a Service (PaaS) deployments, where the underlying infrastructure is managed by Microsoft.

For best collection performance, host the WISdom Data Collector VM in the same Azure region as the monitored instances. Hosting the collector outside Azure is supported but may introduce latency that affects collection reliability.

Collection accounts for Azure environments are typically Microsoft Entra ID (formerly Azure AD) accounts or SQL Authentication accounts.

Azure Managed Instance

SA permissions are available on Azure Managed Instances and will provide full collection capability. Where least privilege is preferred, configure the permissions below.

Custom server roles are supported on Azure Managed Instance and follow the same pattern as on-premises SQL Server 2022.

Master Database

USE [master];
GO

-- Optional: Create a custom server role for WISdom monitoring
-- Skip this block if granting permissions directly to the collection account
CREATE SERVER ROLE [WISdom_Server_Monitor];
GO

-- Add the required built-in server roles to the custom server role
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [WISdom_Server_Monitor];
ALTER SERVER ROLE [##MS_DefinitionReader##] ADD MEMBER [WISdom_Server_Monitor];
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [WISdom_Server_Monitor];
GO

-- Grant explicit master database permissions to the custom server role
GRANT VIEW ANY ERRORLOG TO [WISdom_Server_Monitor];
GRANT ALTER TRACE TO [WISdom_Server_Monitor];

GRANT EXECUTE ON xp_readerrorlog TO [WISdom_Server_Monitor];
GRANT EXECUTE ON xp_instance_regenumvalues TO [WISdom_Server_Monitor];
GRANT EXECUTE ON xp_enumerrorlogs TO [WISdom_Server_Monitor];
GRANT EXECUTE ON xp_regread TO [WISdom_Server_Monitor];
GO

-- Optional: Add the collection account to the custom server role
ALTER SERVER ROLE [WISdom_Server_Monitor] ADD MEMBER [YourCollectionAccount];
GO

MSDB Database

WISdom requires access to specific monitoring data within msdb. You can satisfy these requirements using Option A (Role-Based) or Option B (Explicit Object Permissions).

Option A: SQL Agent Role-Based Configuration (Recommended)

USE [msdb];
GO

-- Optional: Create a custom database role for WISdom monitoring
-- Skip this block if granting permissions directly to the collection account
CREATE ROLE [db_wisdom_collector];
GO

-- Add the custom role to the built-in SQL Agent role
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [db_wisdom_collector];
GO

-- Grant explicit SELECT permissions on the remaining non-Agent tables
GRANT SELECT ON sysmail_event_log TO [db_wisdom_collector];
GRANT SELECT ON sysmail_allitems TO [db_wisdom_collector];
GRANT SELECT ON log_shipping_monitor_primary TO [db_wisdom_collector];
GRANT SELECT ON log_shipping_primary_secondaries TO [db_wisdom_collector];
GRANT SELECT ON sysalerts TO [db_wisdom_collector];
GRANT SELECT ON suspect_pages TO [db_wisdom_collector];
GRANT SELECT ON backupset TO [db_wisdom_collector];
-- sysoperators is not included in SQLAgentReaderRole and must be granted explicitly
GRANT SELECT ON sysoperators TO [db_wisdom_collector];
GRANT EXECUTE ON agent_datetime TO [db_wisdom_collector];
GO

-- Optional: Add the collection account to the custom database role
ALTER ROLE [db_wisdom_collector] ADD MEMBER [YourCollectionAccount];
GO

Option B: Explicit Object Permissions

USE [msdb];
GO

-- Optional: Create a custom database role for WISdom monitoring
-- Skip this block if granting permissions directly to the collection account
CREATE ROLE [db_wisdom_collector];
GO

GRANT SELECT ON sysmail_event_log TO [db_wisdom_collector];
GRANT SELECT ON sysmail_allitems TO [db_wisdom_collector];
GRANT SELECT ON log_shipping_monitor_primary TO [db_wisdom_collector];
GRANT SELECT ON log_shipping_primary_secondaries TO [db_wisdom_collector];
GRANT SELECT ON sysalerts TO [db_wisdom_collector];
GRANT SELECT ON suspect_pages TO [db_wisdom_collector];
GRANT SELECT ON sysoperators TO [db_wisdom_collector];
GRANT SELECT ON sysjobhistory TO [db_wisdom_collector];
GRANT SELECT ON sysjobschedules TO [db_wisdom_collector];
GRANT SELECT ON sysschedules TO [db_wisdom_collector];
GRANT SELECT ON sysjobs TO [db_wisdom_collector];
GRANT SELECT ON syscategories TO [db_wisdom_collector];
GRANT SELECT ON sysjobsteps TO [db_wisdom_collector];
GRANT SELECT ON backupset TO [db_wisdom_collector];
GRANT EXECUTE ON agent_datetime TO [db_wisdom_collector];
GO

-- Optional: Add the collection account to the custom database role
ALTER ROLE [db_wisdom_collector] ADD MEMBER [YourCollectionAccount];
GO

Azure SQL Database

Custom server roles are not available on Azure SQL Database. All permissions must be granted directly to the collection account or a database-level role. The collection account must exist in the master database and in each monitored user database.

USE [master];
GO

ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [YourRoleOrAccount];
ALTER SERVER ROLE [##MS_DefinitionReader##] ADD MEMBER [YourRoleOrAccount];
-- ##MS_DatabaseConnector## allows the account to connect to user databases.
-- VIEW DATABASE STATE is additionally required to read DMVs and state data within each database.
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [YourRoleOrAccount];
GO

VIEW DATABASE STATE must be granted in master and in every user database that will be monitored. Run the following in each target database:

-- Run in [master] and in each monitored user database
GRANT VIEW DATABASE STATE TO [YourRoleOrAccount];
GO

Query Store Permissions (Optional)

Applies to both Azure Managed Instance and Azure SQL Database. When Query Store is enabled on a user database, WISdom uses it for more efficient query statistics collection. The collection account must be added to each database where Query Store is enabled.

-- Run in each user database where Query Store is enabled

-- Optional: Create a custom database role for Query Store access
-- Skip this block if granting permissions directly to the collection account
CREATE ROLE [db_wisdom_qs_collector];
GO

-- For Azure SQL Database only: include VIEW DATABASE STATE in the role
-- so all per-database permissions are consolidated here
-- GRANT VIEW DATABASE STATE TO [db_wisdom_qs_collector];

GRANT SELECT ON sys.database_query_store_options TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_context_settings TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_plan TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query_text TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_query_hints TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_wait_stats TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_runtime_stats TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.query_store_runtime_stats_interval TO [db_wisdom_qs_collector];
GRANT SELECT ON sys.database_query_store_internal_state TO [db_wisdom_qs_collector];
GO

-- Optional: Add the collection account to the custom database role
ALTER ROLE [db_wisdom_qs_collector] ADD MEMBER [YourCollectionAccount];
GO