March 17, 2015 at 10:42 am
"Run as administrator" helps only on the local SQL Server instance. For remote connections you will need to be in Administrators group on the remote server and there is no need to use "Run as administrator" feature.
In fact I login to my workstation as a regular user without any additional rights on this workstation or any remote server. To administer all SQL Servers I use "Run as different user" feature to run SSMS and then provide a seperate domain account with administrative privilliges on all remote servers. Works fine for all servers and I see green arrow on all of them except for DMZ servers because they are members of the different domain, so I have to use SA account to connect.
March 17, 2015 at 11:13 am
I did the same thing. Two accounts, I mean. Thanks for the feedback on that.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2015 at 4:48 pm
I usualy do not reply with a link but in this case the information is too much and too complex to repeat it right here,
You can find relevant information about this subject on:
It might not be completely up-to-date but I guess most of it still holds for the current environment where SQL Server runs.
March 19, 2015 at 12:35 pm
Even though, this is a 3 year old thread but just would like to add that I agree with Jeff, my ID was part of the local admin, I was the sysadmin on my account in SQL but still , I couldnt see the green sign.
Once the run as administrator on SSMS used, it was all ok.
Just my two cents
B
March 19, 2015 at 5:04 pm
I'm a richer man thanks to your 2 cents. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2015 at 5:04 am
Add a firewall exception on the sql server machine for port TCP 49154
June 16, 2016 at 11:43 am
The shift right click on the server from my local machine did the trick for me. That is it allowed me to now start/stop/restart service, thanks Jeff.
June 16, 2016 at 12:54 pm
If you're missing the green arrow, this might help you: Get missing green arrow
June 16, 2016 at 1:14 pm
abeljda 60974 (6/16/2016)
The shift right click on the server from my local machine did the trick for me. That is it allowed me to now start/stop/restart service, thanks Jeff.
You bet. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2017 at 2:13 pm
I have a cluster of two servers (INFRA-SRV1 and INFRA-SRV2) configured as a cluster that runs a SQL Server instance (INFRA-DB1). Even after reading through this entire thread _and_ having a ticket open with Microsoft Premier Support for more than a week, I cannot get Management Studio on my desktop to look the same as Management Studio on the server. The little circles on the icons for the database and the SQL Agent are all white on the desktop yet have green arrows when run on the server. *Sigh* It appears that there are some time you just need to connect to the server to do administration through Management Studio.
June 9, 2017 at 2:43 pm
Ken Hemmerling - Friday, June 9, 2017 2:13 PMI have a cluster of two servers (INFRA-SRV1 and INFRA-SRV2) configured as a cluster that runs a SQL Server instance (INFRA-DB1). Even after reading through this entire thread _and_ having a ticket open with Microsoft Premier Support for more than a week, I cannot get Management Studio on my desktop to look the same as Management Studio on the server. The little circles on the icons for the database and the SQL Agent are all white on the desktop yet have green arrows when run on the server. *Sigh* It appears that there are some time you just need to connect to the server to do administration through Management Studio.
If you ran SSMS as administrator and different domains aren't an issue then check for permissions on cimv2 - Computer Management -> WMI Control -> properties. Security tab and expand root, go to cimv2 and click the security button on the bottom.
This explains how it works and why that lack of permissions can be a problem:
Service status watcher in SQL Server Management Studio – How it works
You could have policies in place that prevent those permissions or you may not be able to even check them. You'd need to check with the group managing that area of security at your company
Sue
June 9, 2017 at 2:54 pm
Ken Hemmerling - Friday, June 9, 2017 2:13 PM*Sigh* It appears that there are some time you just need to connect to the server to do administration through Management Studio.
True dat. 😉
Contrary to what most people claim to be a "Best Practice", I always have and RDC open on the server with SSMS up and running. It has saved my butt several times. When others can't login for one reason or another, I'm already logged in. If you don't have the trivial bit of memory to dedicate to that, my recommendation is that you're being penny wise and pound foolish.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2017 at 3:14 pm
On the other side of that coin, I'll use SSMS from the server running the instance if necessary, but I generally discourage others from doing it.
To pick just one reason, I've seen a remarkable number of people blow up a C drive from mistakenly running a query that returns far more rows than they expect.
I'd rather that happen to a client machine 🙂
June 9, 2017 at 3:26 pm
Sue_H - Friday, June 9, 2017 2:43 PMIf you ran SSMS as administrator and different domains aren't an issue then check for permissions on cimv2 - Computer Management -> WMI Control -> properties. Security tab and expand root, go to cimv2 and click the security button on the bottom.
This explains how it works and why that lack of permissions can be a problem:
Service status watcher in SQL Server Management Studio – How it works
All computers (desktop and the nodes of the cluster) are on the same domain (we only have one for the entire company of 250 people). On each node of my cluster (INFRA-SRV1 and INFRA-SRV2) I went to WMI > Root\cimv2 > Security > Add > my personal account > "Enable Account" is checked by default, added check to "Remote Enable" > OK > OK. Restarted SSMS on my desktop, connect to INFRA-DB1, no change. So... yeah, tried that, doesn't work. Thanks anyway.
June 9, 2017 at 3:33 pm
Ken Hemmerling - Friday, June 9, 2017 3:26 PMAll computers (desktop and the nodes of the cluster) are on the same domain (we only have one for the entire company of 250 people). On each node of my cluster (INFRA-SRV1 and INFRA-SRV2) I went to WMI > Root\cimv2 > Security > Add > my personal account > "Enable Account" is checked by default, added check to "Remote Enable" > OK > OK. Restarted SSMS on my desktop, connect to INFRA-DB1, no change. So... yeah, tried that, doesn't work. Thanks anyway.
Thanks for posting back. You could try restarting Windows Management Instrumentation services also - I think it has to be restarted for any changes to take affect. But if nothing else, hopefully MS gets back to you and then you can let us know whatever they found was the issue.
Sue
Viewing 15 posts - 76 through 90 (of 94 total)
You must be logged in to reply to this topic. Login to reply