A monitoring account needs to read server health, and somebody gives it sysadmin. Granular server roles added in SQL Server 2022 let you grant that access with a much narrower permission set.
Start Granular Server Roles With the Monitoring Queries
I ask for the actual monitoring query list before choosing granular server roles. Processor checks, security inspection, and schema inventory do not require identical rights. Identify which views and functions each query uses. Then read their documented permission requirements for the SQL Server version you operate.
SQL Server 2022 introduced additional fixed server roles with the ##MS_ prefix. They divide state inspection, definition access, connectivity, and selected management tasks. These roles remain available in SQL Server 2025. Use their exact names, including both pairs of number signs, when changing membership.
A monitoring login belongs to an account or group that already exists on the instance. This post uses an existing login named MonitoringLogin. Substitute your approved login when applying the examples. Run membership changes through an administrator authorized to grant those rights. A permission example should not manufacture a shared password.
Distinguish Performance State From Broader State
The ##MS_ServerPerformanceStateReader## role grants VIEW SERVER PERFORMANCE STATE. It covers performance DMVs protected by that permission. The broader ##MS_ServerStateReader## role includes performance, security, and general server-state permissions. Choose the narrower reader when its access covers the monitoring queries.
SQL Server 2022 changed the permission requirement for several performance views. A script copied from older documentation needs a current permission review. Granting an older familiar permission without checking the view can leave a new monitoring account unable to run its queries.
ALTER SERVER ROLE [##MS_ServerPerformanceStateReader##]
ADD MEMBER [MonitoringLogin];The ##MS_ServerSecurityStateReader## role targets security-state inspection. Do not add it merely because a performance query failed. Read the specific error and the view's requirements. A widening permission ladder is an expensive way to avoid reading one documented requirement.
Grant Definition Access Only When Needed
Definition readers serve a different purpose from state readers. ##MS_DefinitionReader## provides broad definition visibility. The more focused ##MS_PerformanceDefinitionReader## and ##MS_SecurityDefinitionReader## roles divide that visibility by purpose. An account that inspects performance metadata deserves a review before receiving all definitions.
Monitoring can expose sensitive information even through read access. Query text, plans, names, and security metadata reveal details about the application. Decide who can inspect that material and where collected output is retained. The absence of write permission does not make every collected result harmless.
SELECT name, type_desc, is_fixed_role
FROM sys.server_principals
WHERE type = 'R'
AND name LIKE N'##MS[_]%'
ORDER BY name;This query inventories the roles present on your instance. Run it with adequate metadata visibility. A limited account's catalog results can omit principals it cannot see. Use the authorized security-review connection for a complete membership audit rather than treating an empty result as proof of absence.
Treat Database Connectivity as a Separate Choice
The ##MS_DatabaseConnector## role grants access to connect across databases without requiring an individual user in each one. That broad reach can be appropriate for an estate-wide monitor. It can also exceed the scope of an account intended to inspect only one application database.
A server state or definition permission can propagate into databases where the login has access. Connectivity and data access still need separate consideration. Connecting to a database does not grant SELECT on every application table. Check the intended database scope before adding the connector role.
For a database-specific account, create a mapped user in that approved database instead. The following example uses an existing scratch database named MonitoringLab. Add only the permissions needed by the particular database queries. The inherited performance-state access comes from the server role already assigned.
USE [MonitoringLab];
GO
CREATE USER [MonitoringLogin] FOR LOGIN [MonitoringLogin];Run CREATE USER only when that user does not already exist. A database-level DENY CONNECT can override connectivity supplied by the connector role for a mapped user. Review explicit denies when troubleshooting access. Do not erase them simply to make a monitoring script succeed.
List Direct Members of the Granular Server Roles
Join sys.server_role_members to the role and member principals. A left join keeps roles with no direct members visible. This query reports direct membership in the new roles. It provides a useful starting inventory for a security review, including account types and empty role assignments.
SELECT r.name AS RoleName, m.name AS MemberName,
m.type_desc AS MemberType
FROM sys.server_principals AS r
LEFT JOIN sys.server_role_members AS rm
ON rm.role_principal_id = r.principal_id
LEFT JOIN sys.server_principals AS m
ON m.principal_id = rm.member_principal_id
WHERE r.type = 'R'
AND r.name LIKE N'##MS[_]%'
ORDER BY r.name, m.name;A Windows group member represents access granted through that group. This output does not expand the group's individual Windows members. It also does not calculate all effective rights from direct grants, other roles, ownership, or group tokens. Investigate those paths when the inventory and observed access disagree.
Test Under the Actual Identity
I test the monitoring account's connection after the grant. An administrator's successful query proves little about the account running the scheduled monitor. Connect using that identity and run its approved query list. Check both successful reads and expected denials, including access outside the intended scope.
SELECT ORIGINAL_LOGIN() AS OriginalLogin,
SUSER_SNAME() AS CurrentLogin,
HAS_PERMS_BY_NAME(NULL, NULL,
'VIEW SERVER PERFORMANCE STATE') AS CanReadPerformanceState,
HAS_PERMS_BY_NAME(NULL, NULL,
'VIEW SERVER SECURITY STATE') AS CanReadSecurityState;
SELECT scheduler_id, runnable_tasks_count, current_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';Permission functions report effective rights for the current context. NULL can indicate an invalid permission or unavailable securable context, so interpret it explicitly. Run the same checks on the target engine version. The scheduler query then exercises an actual performance-state requirement instead of relying only on membership labels.
Keep Management Roles Away From Passive Monitoring
The similarly named ##MS_ServerStateManager## adds state-changing rights. ##MS_LoginManager## can manage logins. ##MS_DatabaseManager## can create databases and gains powerful ownership rights over databases it creates. Those responsibilities belong to deliberately approved administrative identities.
Passive monitoring does not require those management capabilities by default. Keep remediation automation separate when it needs write access. Define exactly which actions it performs and who approves changes to that automation. A health check that can clear shared caches deserves a different review from one that reads scheduler counters.
Remove Granular Server Roles When the Need Ends
Which query justifies each assigned role today? Record that answer with the account owner and review date. Revisit it when monitoring scripts change or an account is retired. Remove obsolete membership deliberately, then rerun the retained queries to confirm their required access still works.
ALTER SERVER ROLE [##MS_ServerPerformanceStateReader##]
DROP MEMBER [MonitoringLogin];The removal above reverses the example grant. It does not remove rights granted through another group or role. Granular server roles make permissions easier to explain, but effective-access tests complete the review. Keep both the membership record and the observed query results.
Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Security Risk of Public Role: Very Little.
Monitoring access is not an administrative entitlement, it is permission to run a defined set of checks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
