Solved

Microsoft SQL and Activity Monitor

Posted on 2014-12-23
7
135 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 47

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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 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