Activity Monitor on SQL

SQL Server Administration
1

Hi All,

I am very new SQL, and have been asked by the company I am working for to help with their SQL issues, and could do with some guidance.

Some background info:

  • Operating System: Windows Server 2012 R2
  • Application: SQL Server 2012 R2 running approx. 10 databases

The Server is part of a domain, therefore we have groups containing the relevant users who have access to the relevant Databases.

Issue:
Now there are three users who have access to all of the databases and they would like to be able to use Activity Monitor from their local machines.

In order to give just the three users access, I did the following:

  • In Active Directory, created the Group "SQL-Activity-Mon-UK12R2"
  • Added the three users to the group
  • On the SQL Server, created new Login for the group "SQL-Activity-Mon-UK12R2", used the default settings.

I found an article on the net that mentions you need to add the users to the Local Group "Performance Monitor Users" and then run Grant View Server State To Domain\users, in my case I ran Grant View Server State To "ents\SQL-Activity-Mon-UK12R2".

The users complained that they get an Access Denied message when trying to open the Activity Monitor.

I added my username to the Group and tested it, and I also get the error message which is:

"The Activity Monitor is unable to execute queries against server UK12R2. Additional Information: (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlid)"

I can´t for the life of me figure out what I can do to resolve this issue.

Would appreciate any help.

Thanks,

Steve

2

Looks like this is the fix for your issue.

3

Thanks for your reply ahmeds08, I followed the steps in the link you provided, but now I get the error "The Activity Monitor is unable to execute queries against server UK12R2. (system.Management)"

Whatever that means....

Hope you can help.

Thanks,

Steve

5

The content item may be relevant. As you have only one way to enable DCOM permission. You go through this for brief description about this:
https://connect.microsoft.com/SQLServer/feedback/details/770897/after-installing-sp1-sql-server-2012-activity-monitor-gives-access-denied

6

Thanks for the info jason_clark, i actually managed to get a test SQL Server up and running in a test environment, and do some testing and it looks like I made a syntax error.....

On our test SQL Server , after adding a test account to the Performance Monitor Users group, I only need to excute the query "Grant View Server State To [ents\SQL-Activity-Mon-UK12R2]" and this seems to work on the test SQL Server.

Will try it on the production SQL Server and see how that flies!

Thanks for your help.

Steve