A few service stop/start/restart questions on SQL Server 2005 SP2, which
I'll call SQLS.

It looks as if there are *potentially* 6 ways to start/stop SQLS
Services like the engine itself, integration services, reporting
service, Agent..

-SQLS Configuration Manager
-SQLS Surface Area Configuration (for Services and Connections)
-Mgmt Studio Local (on server)
-Mgmt Studio Remote (on client)
-Windows Control Panel->Admin Tools->Services
-Command Prompt (ala net start MSSQLSERVER)

By policy, I am /not/ Administrator on the server. But I am SysAdmin
role in SQLS. I have had various levels of success starting/stopping
services in the ways listed above. In some I get Access denied, and in
others I get no msg and it simply doesn't work.

Is there some special non-Admin OS group I need to be in to start/stop
services? Is this handled differently in the different interfaces
listed above?

It seems like my best success for starting/stopping the engine and Agent
is in /local/ Mgmt Studio, but /not/ remote Mgmt Studio - the options
are greyed out on a remote client. Is this by design? Is it a SQLS
bug?

I'm sure I'm not the only SQLS DBA who does not have Admin rights on his
server who wants to start/stop services. Generally speaking, how is
this intended to work?

Any help appreciated.

Allen Jantzen
A freshly minted DQLS DBA

aj (ronald@mcdonalds.com) writes:
> It looks as if there are *potentially* 6 ways to start/stop SQLS
> Services like the engine itself, integration services, reporting
> service, Agent..
>
> -SQLS Configuration Manager
> -SQLS Surface Area Configuration (for Services and Connections)
> -Mgmt Studio Local (on server)
> -Mgmt Studio Remote (on client)
> -Windows Control Panel->Admin Tools->Services
> -Command Prompt (ala net start MSSQLSERVER)

There is a recommendation that you should not start/stop SQL Server
through the Services applet, although I don't know exactly what's
behind it. You are supposed to use any of the SQL tools. (So that
rules out NET START MSSQLSERVER as well.)

By the way, there is a seventh way: you can start SQL Server directly
from the command line. Then again, then it does not run as a service.

> Is there some special non-Admin OS group I need to be in to start/stop
> services? Is this handled differently in the different interfaces
> listed above?

That's more a Windows question, and I don't know the answer.

> It seems like my best success for starting/stopping the engine and Agent
> is in /local/ Mgmt Studio, but /not/ remote Mgmt Studio - the options
> are greyed out on a remote client. Is this by design? Is it a SQLS
> bug?

I guess it's by design. Overall, I don't know if you can start and
stop Windows services remotely.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx