SQL Instance Collection

This page covers the SQL Server permissions required for WISdom data collection on on-premises instances and SQL Server hosted on cloud VMs. Permissions are organized by SQL Server version.

If the collection account holds sysadmin (SA) rights, no additional configuration is needed. If SA is not available or not permitted, configure the least-privilege permissions below for the applicable version.

SQL Server 2019 and Earlier

All User Databases

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

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

-- Grant required server-level permissions
GRANT VIEW SERVER STATE TO [WISdom_Server_Monitor];
GRANT VIEW ANY DEFINITION 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: 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

If your security policy restricts the use of built-in SQL Agent roles, grant explicit permissions on all required objects instead.

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

SQL Server 2022 and Later

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

Query Store Permissions (Optional)

Applies to all SQL Server versions. When Query Store is enabled on a user database, WISdom uses it for more efficient query statistics collection with reduced server impact compared to DMV-based collection. WISdom automatically detects and uses Query Store when these permissions are in place.

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

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