Problem remotely stopping and starting services in SQL Server 2005

Hi guys

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?

Any help would be appreciated.

Cheers

HanleyI like the easy questions!

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 (http://www.windowsitpro.com/Articles/ArticleID/48881/48881.html?Ad=1) 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.

-PatP|||Using group policy I assigned my user account (SQLTest) Start, Stop and Pause permissions to MSSQLSVC and SQL Server Agent.

When I load management studio using this ID I'm still unable to stop or start the services. If I put the ID into local admin it works okay?

2nd Question - even with my ID in the local administrators group it doesn't work with the firewall turned on, am I missing any exceptions, these are what I have:

Program Exceptions

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe:*:enabled:M S SQLServer

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.exe:*:enabled:MS DTS

C:\Program Files\Microsoft SQL Server/90\Shares\sqlbrowser.exe:*:enabled : SQL Browser

Port Exceptions

1433:TCP:*:enabled : SQL Server
1434:UDP:*:enabled : SQL Instance
2725:TCP:*:enabled : SQL Analysis Services
135:TCP:*:enabled : SQL Integration Services

As said above, even using an ID with local admin permissions to the server I still cannot remotely stop and start SQL services with the firewall turned on.