locked
SQL 2005 SSMS Object Explorer - Server level 'green arrow head' enabling remote restart of server RRS feed

  • Question

  • If there is a little green arrow head in a little circle on the server's cylindrical icon then I can restart remote servers from with SSMS.   I am eager to reduce the amount of work I do through remote desktop.  What controls whether I get this 'green arrow head'?   At present, I see it for some servers but not others.    I would like this ability for all my servers. 

    It does not appear to be related to sql server's server level roles.  I can have sysadmin privilege BUT will not necessarily get the little green arrow head.  How can I control access to this functionality while granting myself minimal privileges?

    Many thanks

    Breadon Hill

    Friday, November 18, 2011 10:36 AM

Answers

  • Deleted
    • Marked as answer by bredon Friday, November 25, 2011 3:02 PM
    Tuesday, November 22, 2011 3:12 PM

All replies

  • Hi,

    We see something similar with our servers if:

    - The SQL Server you've registered in SSMS is clustered

    - The SQL Server you've registered is a different version than SSMS, i.e. SSMS 2008, SQL Server 2005 (though it works most of the time)

    Either of these fit the bill?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Friday, November 18, 2011 10:55 AM
  • Thanks Andrew.  Alas, that does not seem to be quite what I have here.   We don't have clustering (alas).

    I have little green arrowheads on:

    One sql server 2005 on my local domain BUT not on other sql server 2005s on my local domain.  The one with the little green arrowheads is also the domain controller.
    One sql server 2008 R2 on my local domain.  This is the only sql server 2008 R2 and is a development machine.
    One sql server 2000 on a different remote 'trusted' domain but not the other sql server 2005s on that domain.

    All sql server 2005 is at build level 9.00.4035.00.  The loan sql server 2000 is at build level 8.00.760.  The loan sql server 2008 R2 is at build level 10.50.2500.0.

    Suspect it is something to do with the ability to restart services but can not figure out how to control it. 

    Thanks, Breadon.

     

     

     

     

    Friday, November 18, 2011 12:01 PM
  • Thanks Shawn. 

    I have done some experiments on my development server.  If I add myself into the builtin administrators group then I can restart the services.   This is with the sql service continuing running as LocalSystem.  I did try various other changes BUT they seem to be irrelevant.  Not happy about putting myself in builtin administrators and have removed myself for now.   

    Hence, on my development server first, I am going to try creating dedicate domain accounts for the various sql server services.  Have got permission to do this but it will take a day or so.  When this is done I should be able to test your solution better.

    Many thanks, Breadon

    Monday, November 21, 2011 11:40 AM
  • I have changed the sql server service account to use a new dedicate domain user account.  All done though SQL Server Configuration Manager and service was restarted.  Alas it still works the same way.  I have to be in the local administrators group on the server in order to have the service restart 'green arrowhead' displayed on SSMS on my client pc.  I tried adding the new service account into local administrators but this did not work - it had to be my account that was in the local administrators group.

    Also, found web reference:  http://msdn.microsoft.com/en-us/library/ms188690.aspx

    This is entitled 'How to: Configure WMI to Show Server Status in SQL Server Tools'.  Alas this did not seem to fix the problem either

    Running out of ideas - would appreciate more suggestions.  Thanks, Breadon

    Tuesday, November 22, 2011 2:09 PM
  • Hello Shawn

    Following through on your reference to 'a good review of' sc.exe, I am getting somewhere.  Not there yet but am on my way.  Figured out a read only command to see if I can 'talk' to the sql server service using sc:

    locally:  sc qdescription MSSQLSERVER
    remote: sc \\hostname qdescription MSSQLSERVER

    Also, figured how to get the SID of a suitable group to allocate permissions to:

    dsquery group -samid MyGroupName | dsget group -sid

    Alas, that's all I have time to do on this today BUT please don't think I am ignoring you.  I am very grateful for your suggestions and will get this cracked. 

    Thanks again, Breadon.

    Wednesday, November 23, 2011 1:39 PM
  • Hi Shawn, I am getting somewhere!  Following through from your reference, I used the "sc sdset" command to give our sql privileged user group suitable privileges on the remote development server:

    SDMANAGER - had to do this first
    MSSQLSERVER
    SQLSERVERAGENT - had to do this as well as MSSQLSERVER, the services are linked

    On my local pc, I am now able to issue command shell commands such as:

    sc \\remoteserver stop MSSQLSERVER
    sc \\remoteserver stop SQLSERVERAGENT

    I can also stop and start the services on the remote computer using 'Windows Computer Management', see http://technet.microsoft.com/en-us/library/ms190622.aspx

    However, I still do not have a little green arrow besides the server name when I run SSMS on my local pc....

    That said, I am miles futher on than before.  Plus am reasonably sure I will be able to setup desktop shortcuts for suitably privileged users to stop and start remote services not related to sql server.  This will be very useful.

    Am still working on the little arrows.  Thanks again,  Breadon.

     

    Friday, November 25, 2011 3:02 PM
  • In my case, this had something to do with name resolution.

    The green arrow did not display until the name of the server displayed in SSMS matched the hostname used to initiate the connection.

    Once we got the proper entries set in our third party DNS server, the green arrow appeared.

    Tuesday, February 21, 2012 3:49 PM
  • I was concerned about using sc commands on live servers.  Its rather cryptic command line stuff.  So, though I did get the sc mechanism working on our dev servers, I found a different approach on the live servers.  I have a separate admin account that is in the local administrators group on our servers.  Thus I was able to use runas command lines to execute SSMS and compmgmnt.msc (MMC).  Compmgmnt is the light weight option if your server is in trouble.  You need to right click on the left hand pane heading 'Computer Management (Local)', choose 'Connect to another computer ...' and then log in to the server that needs attention.

    Type the following into a command shell prompt:

    %SystemRoot%\System32\runas.exe /user:AAA\ADM.FBloggs "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

    Realistically, I suggest defining a desktop short cut with the command line as a link.


    For MMC try:


    %SystemRoot%\System32\runas.exe /user:AAA\ADM.FBloggs "%SystemRoot%\System32\mmc.exe    %SystemRoot%\System32\compmgmt.msc"


    You may need to change your local policies to enable elevated privileges.  From start menu, enter SECPOL.msc.  Under Security Settings/Local Policies/Security Options locate 'User Account Control: Run all administrator's in Admin Approval Mode' and Disable it.  

     SQL Server options are under 'Services and Applications'.  If you need to manage a remote computer, right click on 'Computer Management (Local)' and choose 'Connect To Another Computer'.

    Thanks to everyone for their help with this.

    Breadon

    Tuesday, February 21, 2012 5:21 PM