WISdom monitors multiple SQL Server deployment types, each requiring specific permissions for successful data collection. This section covers permission requirements for on-premises and cloud-hosted SQL instances, Azure SQL, and Amazon RDS environments.
Permissions Overview
Historically, the collection account was a member of the sysadmin (SA) role, which provides full access to all SQL data. Where SA is available and permitted, it remains the simplest configuration. Where it is not, WISdom supports a least-privilege alternative for each deployment type.
With SQL Server 2022, Microsoft introduced dedicated server roles that grant access to system tables and Dynamic Management Views (DMVs) without exposing user databases or data. This provides a more secure and supportable alternative to SA for modern environments. For background on these roles, see Andreas Wolter's documentation on the SQL Server Blog.
Recommended Setup Pattern — Custom Monitoring Roles
Rather than granting permissions directly to a service account, create dedicated roles and assign the required permissions to those roles. Add the collection account as a member of each role. This approach:
- Isolates WISdom permissions from the account identity, making it straightforward to rotate or replace the service account without re-granting permissions
- Provides a single point of change when WISdom permission requirements are updated
- Mirrors standard enterprise monitoring patterns and simplifies audit reviews
Depending on your environment and whether Query Store is in use, you will create two or three roles:
Role 1 — Custom Server Role (on-premises, EC2, Azure MI only)
A server-level role covers the Master Database permissions required for WISdom collection. This role is created once per instance in the context of master.
| Environment | Supported | Suggested Name |
|---|---|---|
| SQL Server (on-premises / EC2 / Azure VM) | Yes | WISdom_Server_Monitor |
| Azure Managed Instance | Yes | WISdom_Server_Monitor |
| Azure SQL Database | No — grant directly to account | — |
| Amazon RDS | No — grant directly to account | — |
Role 2 — Custom MSDB Database Role
A database-level role in msdb covers the SQL Agent and mail monitoring permissions. This role is created once per instance in the context of msdb.
| Environment | Suggested Name |
|---|---|
| SQL Server (on-premises / EC2 / Azure VM) | db_wisdom_collector |
| Azure Managed Instance | db_wisdom_collector |
| Amazon RDS | db_wisdom_rds_collector |
Role 3 — Query Store Database Role (Optional)
When Query Store is in use, a database-level role covers the required sys.query_store_* permissions. Unlike the server and MSDB roles, this role must be created and the collection account added as a member in every user database where Query Store is enabled. It is not a one-time setup.
| Environment | Suggested Name |
|---|---|
| All supported environments | db_wisdom_qs_collector |
The child pages use these role names in their example scripts. Substitute your organization's naming convention as needed. All role creation scripts use GO batch separators and must be run in the correct database context — USE [master] for the server role, USE [msdb] for the MSDB role, and the target user database for each Query Store role.
SQL Instance Collection
Covers on-premises SQL Server instances and SQL Server hosted on cloud VMs (EC2, Azure VMs). SA permissions provide full collection capability. If SA is not available, refer to the least-privilege configuration for the applicable SQL Server version.
Azure SQL Collection
SA permissions are not available for Azure Managed Instances or Azure SQL Databases. This page details the minimum required permissions for both deployment types.
AWS SQL Collection
Covers both EC2 and RDS instances. EC2 follows the same permission model as standard SQL Server. RDS requires specific permissions granted against RDS-specific views rather than core system tables.