Provide access to SQL Server Activity Monitor without giving sysadmin perms

 

Is there any way to provide a user access to the Activity Monitor without enabling them as a sysadmin?  Yes.  Of course there is.  More easily managed if we do it with a server-level role than to multiple individual logins, like this:

     USE master;
     -- Step 1: Create the custom server role
     CREATE SERVER ROLE [ActivityMonitorRole];

     -- Step 2: Grant the necessary permission to the role
     GRANT VIEW SERVER STATE TO [ActivityMonitorRole];

     -- Step 3: Add specific logins to the role
     ALTER SERVER ROLE [ActivityMonitorRole] ADD MEMBER [YOURUSER];
     -- Repeat for additional logins as needed

Change your mind for some reason?  Just as easy to revert.

     -- Step 1: Remove login from the role

     ALTER SERVER ROLE [ActivityMonitorRole] DROP MEMBER [your_user];

     -- Step 2: Revoke VIEW SERVER STATE from the role

     REVOKE VIEW SERVER STATE FROM [ActivityMonitorRole];

     -- Step 3: Drop the role if no longer needed

     DROP SERVER ROLE [ActivityMonitorRole];