Solved

Microsoft SQL and Activity Monitor

Posted on 2014-12-23
7
128 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 45

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 42
pros and cons of RDS vs VDI 12 64
Split Data in 1 column into 2 columns 8 21
Join vs where 2 0
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now