Solved

Microsoft SQL and Activity Monitor

Posted on 2014-12-23
7
143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 50

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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