What are the minimum permissions for Activity Monitor

Activity monitor is something that I do not use very often – it is just easier for me to work with the DMVs and the rest of the tools out there that show you what really is happening on your server. Although I am not using it, there are DBAs and support guys, who count really much on the Activity Monitor in SQL Server Management Studio. And as every DBA should do, they are all trying to access it remotely instead of RDC-ing the database server with Terminal Services. So far so good. If you are a member of the Local Administrators on the DB Server and sysadmin on the particular instance you want to monitor – everything is just great – you just open a management studio, connect to the instance remotely and start your Activity Monitor. What are you going to do if you are not a Local Administrator and you are still a junior DBA, not blessed with sysadmin permissions over the SQL Server instances? Well here comes the interesting stuff. Here is how to enable Activity Monitor for remote connection for non-admins. 

First as we are all thought, you need to have VIEW SERVER STATE permissions on instance level. That is so you can access the numerous DMVs and DMFs that will show you process information, blocking sessions, login information, etc. So far so good – you grant the permissions, open the SSMS and you see that the Processor, Network, Disk IO and Memory graphs are just grayed out. Here comes the second step.

Secondly, add the AD users, using the activity monitor to the local OS group Performance Monitor Users. That is needed because Activity monitor is using number of performance counters to visualize data in those graphs.

Finally, you need one more set of permissions, otherwise the Data File I/O section will not work – those permissions are: VIEW ANY DEFINITION, CREATE DATABASE and ALTER ANY DATABASE. OK, yes, the last two are hard to explain – why one need to create or alter any database, only to see how the datafiles/logfiles are loaded? Well, that is to access the sys.master_files.

So in summary – in order to enable Activity monitor for non-sysadmins and non-local admins (and for remote access) you need to apply the following two actions:

1. Grant on instance level the following permissions: VIEW SERVER STATE, VIEW ANY DEFINITION, ALTER ANY DATABASE, CREATE DATABASE (if you are using SQL Server 2012, you can easily setup a user defined server role)

2. On OS level add the AD users to the group Performance Monitoring Users

 

That is all for today.

For reference, you can check the following article and a connect item:

http://msdn.microsoft.com/en-us/library/ms175518.aspx

http://connect.microsoft.com/SQLServer/feedback/details/350723/ssms-activity-monitor-does-not-show-cpu-time-for-remote-instances

 

3 Thoughts on “What are the minimum permissions for Activity Monitor

  1. Miroslav Dimitrov on Sep 16, 2013 at 21:20 said:

    Great article Vanka :) Can you please share more details how you found out that you need to have these permissions and especially the one with adding the user to the group Performance Monitoring users?

    Thanks.

    • Ivan Donev on Sep 18, 2013 at 09:08 said:

      Thanks Miro!

      Well, it was mainly the two articles I pointed out in the Reference section.Besides, it is kind of logical that you would need perfmon counters for the CPU, Memory, etc. graphs, I just never thought of it that way (activity monitor was something that sysadmins have by default). And to be honest, I do not really like Activity Monitor, I prefer DMVs :)

  2. Matt on Oct 22, 2013 at 16:42 said:

    Afraid this doesn’t work! My users have sysadmin on the SQL box and I’ve added them to both local PerfMon groups on the Windows OS. There are no firewalls involved and activity monitor works fine for a domain admin like myself. I’ve also enabled ports 1024-5000 on DCOM so will see what they say about that

    We get:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    The Activity Monitor is unable to execute queries against server XXXXX.
    Activity Monitor for this instance will be placed into a paused state.
    Use the context menu in the overview pane to resume the Activity Monitor.

    ——————————
    ADDITIONAL INFORMATION:

    The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (mscorlib)

    ——————————
    BUTTONS:

    OK

Leave a Reply

Post Navigation