 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / January 2007Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.
I have a developer who has dbo privileges in master and msdb, and also has VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005 documentation she should be able to view the Activity Monitor, but instead gets the following error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Cannot show requested dialog. ------------------------------ ADDITIONAL INFORMATION: Unable to execute requested command. ------------------------------ Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor) ------------------------------ SELECT permission denied on object 'tables', database 'mssqlsystemresource', schema 'sys'. SELECT permission denied on object 'partitions', database 'mssqlsystemresource', schema 'sys'. Changed database context to '<db1>'. Changed database context to '<db2>'. Changed database context to '<db3>'. Changed database context to '<db4>'. Changed database context to '<db5>'. Changed database context to '<db6>'. Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229) It popped up last week, and I read that Reporting Services running may have an effect on it, so I stopped the service and she was able to get in with no problem. No changes have been made to the server since I stopped the service, but this error just started again today. No updates or patches have been installed on the server recently. I have tried granting permission to 'tables' and 'partition' for master and mssqlsystemresource db's, and to individual system tables but I run into errors every time. I'm pretty new to SQL Server 2005 so please explain everything in very simple terms! THANKS! Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP 1 installed. > I have a developer who has dbo privileges in master and msdb, and also has > VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005 [quoted text clipped - 6 lines] > SELECT permission denied on object 'partitions', database > 'mssqlsystemresource', schema 'sys'. That's a tricky one. It would indicate that there are locks on tables in msqlsystemresource. And mssqlsystemresource is unavailable to everyone, including sa, but if you're in the sysadmin role, the check may be bypassed so you don't see the error. I don't really know what to suggest, but you could try my aba_lockinfo which gives about the same information as the Actvity Monitor, but without any GUI. You can get it from http://www.sommarskog.se/sqlutil/aba_lockinfo.html. Maybe I would be more worried over the locks in mssqlsystemresource. That does not really sound normal to me...  SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > I have a developer who has dbo privileges in master and msdb, and also has > > VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005 [quoted text clipped - 19 lines] > Maybe I would be more worried over the locks in mssqlsystemresource. > That does not really sound normal to me... This morning I restarted the SQL Server service (twice) to try to clear out any locking issues, and I thought maybe it had something to do with tempdb? Anyways as soon as I restarted the service I checked and I think everything was cleared, but the developer still cannot access the Activity Monitor. I've downloaed your tool but am still reading through the explanation of how it works. I've run it and do not see any locks, with all parameters = 0 (the default). Do you have idea what could be causing the problem? I also created a test account with this developer's exact permissions, and (of course) that account gets the same error. I cannot give sa permissions...not sure what to do! > Do you have idea what could be causing the problem? No, I haven't a clue. I created a user with the privileges you mentioned, and it was able to run Activity Monitor on my machine. But there is not much going on at it. > I also created a test account with this developer's exact permissions, and > (of course) that account gets the same error. When you ran the activity monitor or aba_lockinfo? Most of the time, running aba_lockinfo with no parameters gives you the information you want, but you can use the @processes argument to include more processes. My thought was that maybe your user could use aba_lockinfo rather than the Activity Monitor. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Do you have idea what could be causing the problem? > [quoted text clipped - 11 lines] > more processes. My thought was that maybe your user could use aba_lockinfo > rather than the Activity Monitor. The sp gives me a (great) workaround until I can find a solution for this problem. I may be giving Microsoft a call here in the near future. I really appreciate the prompt replies and assistance! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.