1

Some of our end-users still like to use the Activity Monitor in SQL Server. When setting up our SQL-server 2016 servers the activity monitor always ends up in a paused state when the users are not part of the "Administrators" group in Windows (on the server).

When the users are part of the Administrator-group all data gets through (so probably not a Firewall-problem). We also tried:

  • Adding the users to the "Performance Monitor Users" and "Distributed COM Users"
  • In COM: Changing the limits of "My Computer" and the permissions on "Windows Management and Instrumentation".
  • The users also have the "View server state" permission in SQL Server.

Do you have any other suggestions?

Kind Regards,

Tim

  • 1
    I've never seen a satisfying answer to this outside of granting sa access. I'd use this as an opportunity to move those users off using that, because Activity Monitor is bad and if they are smart enough to understand its results, they should be using a better alternative (like sp_whoisactive or sp_blitz mentioned in the link). – LowlyDBA Apr 2 at 14:49
  • Probably because cpu usage is fetched using WMI , and requires OS privileges. I'd report this as bug to Ms and see if they can handle lack of OS privileges more gracefully. – Tibor Karaszi Apr 2 at 16:14
  • @LowlyDBA: We already use the sp_blitz sollution but some of our developpers tend to like the visual graphs in the Activity Monitor. But i might be a good idea to set up a report in PowerBi to make sp_blitz more graphical for the end-users. Thanks. – chittybang Apr 3 at 9:58
  • @TiborKaraszi: Thank you for the tip, found the sollution for this with your tip. I'll post it below. – chittybang Apr 3 at 9:59
1

Found the sollution for this after @TiborKaraszi 's tip about WMI.

  • Run wmimgmnt.msc on the DB-server
  • Open the properties of WMI-Control (RM-click)
  • Open the security tab.
  • Select CIMv2 en click the Security button below
  • Add or locate the account or group
  • In the permissions list, check Remote enable.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.