SQL Instance Collection

Prev Next

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

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.

  • VIEW DATABASE STATE
  • VIEW SERVER STATE
  • EXEC permissions are required to execute the following procedures:
    • xp_readerrorlog
    • xp_instance_regenumvalues
    • xp_enumerrorlogs
    • xp_regread
  • 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
  • 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.

Server Roles:

  • ##MS_ServerStateReader##
  • ##MS_DefinitionReader##
  • ##MS_DatabaseConnector##
  • Grant View Any Error Log
  • Grant EXECUTE on Objects:
    • xp_instance_regenumvalues
    • xp_enumerrorlogs
    • xp_regread
  • 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
  • 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