SQL Instance Collection

Updated
  • Updated on Apr 25, 2025
  • Published on Apr 16, 2025
  • 1 minute(s) read

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

  • VIEW SERVER STATE

  • EXEC permissions are required to execute the following procedures:

    • xp_readerrorlog
    • xp_instance_regenumvalues
    • xp_enumerrorlogs
    • xp_regread

MSDB Database

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

Query Store (optional when used)

  • Optional for user databases where Query Store is enabled.
    • Use for a more efficient collection of Query Statistics information.
    • SELECT permission are required on:
      • QueryStoreTable
      • QueryStoreRuntimeStats
      • QueryStoreWaitStats
      • QueryStorePlan
      • QueryStoreQuery
Query Store Tables for SQL Versions Prior to SQL2016

The above Query Store tables were introduced in SQL 2016.
When monitoring older versions, SELECT rights will need to granted on the system table:

  • sys.query_store_query
  • sys.query_store_plan
  • sys.query_store_runtime_stats.

SQL 2022 and newer SQL Server versions:

Server Roles:
* ##MS_ServerStateReader##
* ##MS_DefinitionReader##

Master Database Permissions:

  • Grant View Any Error Log
  • Grant EXECUTE on Objects:
    • xp_instance_regenumvalues
    • 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
    • sysjobhistory
    • sysjobschedules
    • sysschedules
    • sysjobs
    • syscategories
    • sysjobsteps
    • backupset
  • EXEC permissions on:
    • agent_datetime

Query Store (optional when used)

  • Optional for user databases where Query Store is enabled.
    • Use for a more efficient collection of Query Statistics information.
    • SELECT permission are required on:
      • QueryStoreTable
      • QueryStoreRuntimeStats
      • QueryStoreWaitStats
      • QueryStorePlan
      • QueryStoreQuery