Unanswered: Problem remotely stopping and starting services in SQL Server 2005
I've been dealing with a few problems with SQL Server 2005.
We don't want to give our DBA's local administrator access to the servers so we had a problem with SSIS which I managed to resolve by adding their global group into the local DCOm group and modifying the security properties of MsDtsServer.
Another problem I've come across is when you log into Management Studio as an account that has local administrator access to the server you have the Green symbol next to the registered server, you can then right click and Stop, Restart services etc.
If I log into Management Studio with an account that has full SQL sysadmin rights but not local administrator rights to the server then the Green symbol doesn't appear and the Stop, restart options are all greyed out - all other functionality appears okay, they just seem to lose the ability to remotely stop and start services.
All of the above is with Windows Firewall turned off, if the user has local administrator rights but the SQL Server has the Windows Firewall switched on they are unable to remotely stop and restart services.
Port 1433 is open on the firewall and program execptions are there for sqlbrowse.exe
Anyone come across this particular problem before?
The problem is that only members of the Local Administrators group have permission to start and stop services on a Windows machine by default. You'll have to modify the ACLs on the machine to grant the permission needed to start and stop the service(s). There's a great article at Windows IT Pro with the step-by-step for doing this.
For what it is worth, I would STRONGLY recommend creating a local (machine) group and granting it the necessary privleges via the ACL, then creating a DBA group in AD and making that domain group a member of the local group. While this seems a bit arcane, it means that when you put a login into the DBA group in AD, they inherit the ability to start and stop the SQL Services.