SQL Instance Collection

This page outlines the necessary SQL Server permissions required for data collection, categorized by SQL Server instance and versions.

SQL Server Permissions

Sysadmin (SA) rights ensure that the WISdom collection account has the necessary permissions to gather SQL data. However, a more secure option is to restrict the permissions for the collection account.

For SQL Server 2019 and older versions, the required permissions are still extensive, closely resembling SA rights, as the roles required for the data collection still grant access to user databases and tables.

Starting with SQL Server 2022, Microsoft has introduced specific Server Roles that provide access to system tables and views without granting access to user databases. WISdom requires additional permissions on the Master and MSDB databases. If Query Store is in use, SELECT access on the QueryStoreTable in each user database is also needed.

Non SA Collection Permissions

SQL 2019 and older SQL Server versions:

All Databases:

• VIEW DATABASE STATE

Master Database Permissions

• VIEW SERVER STATE
• VIEW ANY DEFINITION
• ALTER TRACE
• EXEC permissions are required to execute the following procedures:
  • xp_readerrorlog
  • xp_instance_regenumvalues
  • xp_enumerrorlogs
  • xp_regread

MSDB Database Permissions

• SELECT permissions are required on the following tables:
  • sysmail_event_log
  • sysmail_allitems
  • log_shipping_monitor_primary
  • log_shipping_primary_secondaries
  • sysalerts
  • suspect_pages
  • sysoperators
  • sysjobhistory
  • sysjobschedules
  • sysschedules
  • sysjobs
  • syscategories
  • sysjobsteps
  • backupset
• EXEC permissions on:
  • agent_datetime

Query Store Permissions (optional when used)

When Query Store is enabled on user databases, WISdom can leverage it for more efficient query statistics collection with reduced server impact compared to DMV-based collection.

• SELECT permission are required on:
  • sys.database_query_store_options
  • sys.query_context_settings
  • sys.query_store_plan
  • sys.query_store_query
  • sys.query_store_query_text
  • sys.query_store_wait_stats
  • sys.query_store_runtime_stats
  • sys.query_store_runtime_stats_interval
  • sys.query_store_query_hints
  • sys.database_query_store_internal_state

SQL 2022 and newer SQL Server versions:

Server Roles:

• ##MS_ServerStateReader##
• ##MS_DefinitionReader##
• ##MS_DatabaseConnector##

Master Database Permissions

• Grant View Any Error Log
• ALTER TRACE
• Grant EXECUTE on Objects:
  • xp_instance_regenumvalues
  • xp_enumerrorlogs
  • xp_regread

MSDB Database Permissions

• Grant SELECT on Objects:
  • sysmail_event_log
  • sysmail_allitems
  • log_shipping_monitor_primary
  • log_shipping_primary_secondaries
  • sysalerts
  • suspect_pages
  • sysoperators
  • sysjobhistory
  • sysjobschedules
  • sysschedules
  • sysjobs
  • syscategories
  • sysjobsteps
  • backupset
• EXEC permissions on:
  • agent_datetime

Query Store Permissions (Optional)

When Query Store is enabled on user databases, WISdom can leverage it for more efficient query statistics collection with reduced server impact compared to DMV-based collection.

Required Permissions

Grant SELECT permission on the following Query Store system views in each database where Query Store is enabled:

Query Store System Views:

• sys.database_query_store_options
• sys.query_context_settings
• sys.query_store_plan
• sys.query_store_query
• sys.query_store_query_text
• sys.query_store_wait_stats
• sys.query_store_runtime_stats
• sys.query_store_runtime_stats_interval
• sys.query_store_query_hints
• sys.database_query_store_internal_state