7

I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself.

I no longer see the green "running" icons on the server and on the agent, and several menu options are greyed out (Start/Stop/Restart on server menu and agent menu).

enter image description here

Also, when I try to access the Activity Monitor, I get the following error:

enter image description here

Error Detail

I found the following article about the Activity Monitor. I followed the steps there (although I am a local admin on the sql server and should have had all of those permissions already) to no avail. The server is running Windows 2008 R2 Datacenter SP1. When I remote desktop to the server itself and use SSMS directly I have none of these issues, and when a domain admin connects to the server through SSMS remotely everything also works for him.

Any ideas?

|improve this question
7

The behavior you are seeing happens if you aren't recognized as having permissions to manage services on the remote server. This MSDN article describes some possible solutions on how to grant users rights to manage services.

Does the service account for the agent have local administrator permissions on the server? Have you stopped and started database services and the SQL Server agent service on the server since encountering this problem?

|improve this answer
  • That article is for windows server 2003 - will it still apply for 2008? The service account is a managed service account which I've added to the local admin group. I restarted the agent service which didn't do anything but I can't restart database services at the moment since this is a production server. I am a local admin on the server as well. – Mansfield Jul 4 '13 at 19:28
  • 1
    After rebooting the server having made the service accounts local admin, it started working. Thanks! – Mansfield Jul 8 '13 at 12:13
3

I had exactly this issue. everything was working fine until I noticed that the green indicator on the database cylinder logo (next to the instance name) became white instead of green (exactly as you have). Permissions were also fine. I restarted SSMS and connected again to the same instance and issue was solved.

|improve this answer
2

This issue (remotely was white circle, locally was green) happened to me when I had multiple logins defined (windows integrated mode).

Some logins were through a windows security group that had the sysadmin privilege and another was through an individual account that did not have the sysadmin privilege.

The solution was to remove the individual account login that didn't have the sysadmin privilege. The green circle and menu options came back.

|improve this answer
1

I had this locally on a server that was updated from SQL2008R2 to SQL2012 to SQL2014. After first being stunned, the solution was simple: SQL installer does not remove the old SSMS versions. I had links/shortcuts to old SSMS on the taskbar. When using SSMS2014, "standard reports" where available again.

|improve this answer
-1

If you have entered the correct log in info, the issue is most likely linked to the fact that the sqlserver service has been disabled somehow. You can check its status and re-enable it by going to services.msc.

|improve this answer
  • 2
    How would I be able to connect and run queries if the service is disabled? – Mansfield Aug 17 '13 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.