Granting Read-Only Access to Activity Monitor in SQL Server

Granting read-only access to Activity Monitor requires a single permission: VIEW SERVER STATE. Without it, users see an error the moment they try to open Activity Monitor in SSMS. With it, they can monitor live sessions, waits, resource usage, and expensive queries without touching anything they shouldn't.

This is one of the most common access requests in managed SQL Server environments. A developer wants to see why their query is slow. A project manager wants to check whether the database is under load. A client wants visibility into their environment without you handing over sysadmin rights. The solution is straightforward, but it's worth understanding exactly what you're granting, what it covers, and where the limits are.

Why Not Just Grant sysadmin?

It's tempting to take the path of least resistance, especially when someone is standing over your shoulder asking for access. But sysadmin is a sledgehammer. It grants unrestricted control over the entire SQL Server instance, including the ability to drop databases, modify security settings, impersonate other logins, and read data across every database on the server.

VIEW SERVER STATE is a scalpel. It gives users read access to dynamic management views (DMVs) and functions that expose the current state of the server, which is exactly what Activity Monitor uses under the hood.

The principle of least privilege isn't just a security best practice. In managed environments, it's a contractual and operational necessity. Granting excessive permissions to one user can create audit failures, compliance issues, and genuine security risk if that account is ever compromised.

What Does VIEW SERVER STATE Actually Grant?

VIEW SERVER STATE is a server-level permission that allows a login to query a wide range of DMVs and dynamic management functions (DMFs). Activity Monitor in SSMS is essentially a visual wrapper around several of these, including:

  • sys.dm_exec_sessions - active sessions on the server
  • sys.dm_exec_requests - currently executing requests
  • sys.dm_os_wait_stats - cumulative wait statistics
  • sys.dm_exec_query_stats - aggregated query performance data
  • sys.dm_io_virtual_file_stats - I/O statistics per database file
  • sys.dm_os_performance_counters - internal SQL Server performance counters

Without VIEW SERVER STATE, querying any of these DMVs returns either an error or heavily filtered results showing only the user's own sessions. With it, the user can see activity across the entire instance, which is why it's important to grant this only to people who genuinely need it.

Microsoft documents this permission as part of the server-level permissions hierarchy. It does not grant the ability to modify anything, kill sessions, or access the actual data stored in user databases.

How to Grant Read-Only Access to Activity Monitor

The T-SQL is simple. Connect to the SQL Server instance with a login that has sufficient privileges (typically sysadmin or securityadmin), then run the following:

USE [master]
GO
GRANT VIEW SERVER STATE TO [DOMAIN\Username]
GO

Replace [DOMAIN\Username] with the actual Windows login, or use a SQL Server login if applicable:

USE [master]
GO
GRANT VIEW SERVER STATE TO [SQLLoginName]
GO

That's it. The user can now open Activity Monitor in SSMS (right-click the server in Object Explorer, select Activity Monitor) and see live session data, wait statistics, resource usage, and recent expensive queries.

No restart required. The permission takes effect immediately.

Verifying the Permission Was Granted

Before you close the ticket, verify the grant applied correctly. Run this query to confirm:

SELECT 
    pr.name AS LoginName,
    pr.type_desc AS LoginType,
    pe.permission_name,
    pe.state_desc
FROM sys.server_permissions pe
INNER JOIN sys.server_principals pr 
    ON pe.grantee_principal_id = pr.principal_id
WHERE pe.permission_name = 'VIEW SERVER STATE'
ORDER BY pr.name;

This returns all logins currently holding VIEW SERVER STATE. It's a useful query to run periodically as part of a permissions audit. In environments with multiple DBAs or frequent access requests, this list has a habit of growing longer than anyone realises.

What Activity Monitor Can See (and What It Can't)

This is worth explaining clearly to whoever you're granting access to, because it sets expectations and prevents confusion later.

Activity Monitor shows:

  • Active sessions and their current status
  • Waiting tasks and the wait types driving them
  • Database I/O rates
  • Recent CPU-intensive queries with execution plans
  • Resource usage trends over the current monitoring window

Activity Monitor does NOT show:

  • Data stored inside user databases
  • Historical query data beyond what's in the plan cache
  • Detailed index or table statistics
  • SQL Agent job history
  • Configuration details or server properties

If a user needs deeper diagnostic access, such as querying sys.dm_exec_sql_text directly or reading from Query Store, those may require additional permissions depending on the context. But for the typical "I want to see what's happening on the server right now" use case, VIEW SERVER STATE covers everything they need.

Revoking Access When It's No Longer Needed

Access requests have a way of becoming permanent by default. Someone asks for temporary access, gets it, and then nobody removes it when the project ends. Build the habit of revoking permissions when they're no longer required.

USE [master]
GO
REVOKE VIEW SERVER STATE FROM [DOMAIN\Username]
GO

If you're managing a larger environment, consider scheduling a quarterly review of server-level permissions using the verification query above. It takes five minutes and can surface access that should have been removed months ago.

Using a Role-Based Approach for Multiple Users

If you're granting this access to more than one or two people, managing it login by login gets messy quickly. A cleaner approach is to create a server role and assign VIEW SERVER STATE to the role, then add logins to the role as needed.

-- Create a dedicated server role
CREATE SERVER ROLE [ActivityMonitorViewers]
GO

-- Grant the permission to the role
GRANT VIEW SERVER STATE TO [ActivityMonitorViewers]
GO

-- Add a login to the role
ALTER SERVER ROLE [ActivityMonitorViewers] ADD MEMBER [DOMAIN\Username]
GO

This makes auditing straightforward. You can see exactly who has monitoring access by querying role membership, and adding or removing users doesn't require touching the underlying permission grant.

Key Takeaways

  • Granting VIEW SERVER STATE to a login is the correct, minimal way to give read-only access to Activity Monitor in SQL Server. It does not grant the ability to modify data, kill sessions, or access database content.
  • Never use sysadmin as a shortcut for monitoring access. The permission footprint is vastly disproportionate to the need.
  • The permission takes effect immediately with no server restart required.
  • Use the sys.server_permissions query to audit who currently holds VIEW SERVER STATE. In busy environments, this list grows quietly over time.
  • For environments with multiple monitoring users, a custom server role is cleaner and easier to audit than individual permission grants.

Managing permissions correctly across a SQL Server estate is one of those things that looks simple until you're dealing with a security audit or an access-related incident at 2am. If you're not confident your SQL Server environments have been configured with least-privilege principles in mind, a DBA Services health check will surface permission gaps, over-privileged accounts, and configuration risks before they become problems. Get in touch with the DBA Services team to find out what's actually running in your environment.