Solved

Microsoft SQL and Activity Monitor

Posted on 2014-12-23
7
142 Views
Last Modified: 2015-01-10
Hi
Because of securtity I have excluded people from the sysadmin role of the server, but they should still be able to use the Activity Monitor, but it fails(see attached doc).
I have created a new group with the permissions "Alter any database", "Connect SQL", "Create any database", "View any definition" and "View server state" but they still get the error.

Am I missing any permissions?
AM-error.docx
0
Comment
Question by:systemgruppen
  • 4
  • 2
7 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40514934
Why they need to use the Activity Monitor? If you provide them some scripts with DMV's queries it would do the job.
Anyway, the users are AD users ,right? They need to be added to the group Performance Monitoring Users in Windows.
0
 

Author Comment

by:systemgruppen
ID: 40527414
I have made an AD group, which already are a member of the Performance Monitor Users group on the server.
Besides the group also has the following permissions on the SQL server:

1. Alter any databases
2. Connect SQL
3. Create any database
4. View any definition
5. View server state

And they need access to the Activity Monitor, so giving them scripts is not an option.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40530803
SQL Server's BOL states:
To view the Activity Monitor, a user must have VIEW SERVER STATE permission. To view the Data File I/O section of Activity Monitor, you must have CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permission in addition to VIEW SERVER STATE.
So, if you are sure you have given the users all these permissions and still get the error I would contact Microsoft Product Support or post a new entry in Microsoft Connect.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:systemgruppen
ID: 40531079
Just for testing, I created a SQL user, and made the user member of the above mentioned SQL groups, and it works, so it seems to be a problem with the AD groups.
I then changed the AD Security groups from Universal to Domain Local, but no changes  still get the error.
I have to use AD groups, so does any a suggestion to what I can do?????
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40532702
I can only assume that the AD Group does not have all the required permissions.    If that is not the case then you should contact Microsoft Product Support.
0
 

Accepted Solution

by:
systemgruppen earned 0 total points
ID: 40533037
First I tried to change the group from Universal to Domain Local, but that did not help.

Then I deleted the AD group, and then created a new one, but now as a Domain Local and not Universal Group,  applied it to the SQL server, gave it the above mentioned rights and this did the trick.
0
 

Author Closing Comment

by:systemgruppen
ID: 40541805
That solved the problem
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question