Prerequisites for availability groups
Excubius reads availability groups with a plain SQL login that can see server state and definitions, and nothing else. This article gives the script to create it, the permissions it needs and why, and the network paths to open.
The monitoring login
Run the script as a member of sysadmin on the first replica. Choose any login name; the database administrators see it in their logs, so a neutral name such as usr_monitoring works best.
- SQL Server 2016 to 2019
- SQL Server 2022 and 2025
USE [master];
CREATE LOGIN [usr_monitoring]
WITH PASSWORD = N'<long random password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF,
DEFAULT_DATABASE = [master];
GRANT VIEW SERVER STATE TO [usr_monitoring]; -- DMVs, availability group state, error log
GRANT VIEW ANY DEFINITION TO [usr_monitoring]; -- catalog views: groups, replicas, listeners
GRANT CONNECT ANY DATABASE TO [usr_monitoring]; -- optional: per-database checks, no data access
USE [msdb];
CREATE USER [usr_monitoring] FOR LOGIN [usr_monitoring];
GRANT SELECT ON dbo.backupset TO [usr_monitoring]; -- backup age and preference
GRANT SELECT ON dbo.sysjobs TO [usr_monitoring]; -- SQL Agent jobs
GRANT SELECT ON dbo.sysjobhistory TO [usr_monitoring];
GRANT EXECUTE ON dbo.agent_datetime TO [usr_monitoring];
The same script works. Alternatively, replace the first two grants with the fixed server roles that SQL Server 2022 introduced:
USE [master];
CREATE LOGIN [usr_monitoring]
WITH PASSWORD = N'<long random password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF,
DEFAULT_DATABASE = [master];
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [usr_monitoring];
ALTER SERVER ROLE [##MS_DefinitionReader##] ADD MEMBER [usr_monitoring];
GRANT CONNECT ANY DATABASE TO [usr_monitoring]; -- optional
USE [msdb];
CREATE USER [usr_monitoring] FOR LOGIN [usr_monitoring];
GRANT SELECT ON dbo.backupset TO [usr_monitoring];
GRANT SELECT ON dbo.sysjobs TO [usr_monitoring];
GRANT SELECT ON dbo.sysjobhistory TO [usr_monitoring];
GRANT EXECUTE ON dbo.agent_datetime TO [usr_monitoring];
Do not add the login to sysadmin, securityadmin or db_owner, and do not give it db_datareader on user databases. Excubius never needs them. The connection test warns when the login is sysadmin.
Same login on every replica
The login must exist on every replica with the same SID, so it keeps working after a failover.
-
On the first replica, read the SID:
SELECT sid FROM sys.server_principals WHERE name = N'usr_monitoring'; -
On each other replica, create the login with that SID and the same password:
CREATE LOGIN [usr_monitoring] WITH PASSWORD = N'<same password>', SID = 0x…,CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [master]; -
Repeat the grants and the
msdbpart on each replica.msdbis local to every replica.
What each permission is for
| Permission | Needed by |
|---|---|
VIEW SERVER STATE | Replica and database state (sys.dm_hadr_*), performance counters, waits, the error log |
VIEW ANY DEFINITION | Availability groups, replicas, listeners, files, logins, audits |
CONNECT ANY DATABASE | Only per-database checks that run inside each user database. Without it, those checks cover the databases where the login has a user |
msdb grants | Backup age and backup preference, SQL Agent jobs, discovery's SQL Agent summary |
Network
| From | To | Port |
|---|---|---|
| Excubius server | The listener name and every listener IP (multi-subnet listeners have one per subnet) | The SQL port, 1433 by default |
| Excubius server | Every replica by its own name | The SQL port |
| Excubius server | Named instances without a fixed port | UDP 1434 (SQL Server Browser) |
Nothing connects to the Excubius collector; all connections go from it to SQL Server.
Connection behaviour
Connections declare ApplicationIntent=ReadOnly and MultiSubnetFailover=Yes. If the primary is configured with PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE), it refuses read-intent connections to its availability databases, and per-database checks skip them. To include them, set the credential attribute application_intent: ReadWrite. It only changes routing; the login still has no write permission.