2

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

Improve this question
4
  • 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 - John M
    Commented Apr 2, 2020 at 14:49
  • 1
    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
    Commented Apr 2, 2020 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
    Commented Apr 3, 2020 at 9:58
  • @TiborKaraszi: Thank you for the tip, found the sollution for this with your tip. I'll post it below.
    – chittybang
    Commented Apr 3, 2020 at 9:59

1 Answer 1

Reset to default
5

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

  • Run wmimgmt.msc on the DB-server
  • Open the properties of WMI-Control (right-click)
  • Open the security tab.
  • Select CIMv2 and 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 and acknowledge you have read our privacy policy.

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