Hi,

In SQL2000 I had to change permissions to scmanager to tell if SQL2000 was
running on a server after the server was upgraded to W2K3 SP1. This was due
to SP1 security changes.

Now I have SQL2005 Management Studio and I cannot see the status of any of
my SQL2005 servers and only some of my SQL2000 servers.

I am a DBA but my Windows account is not a local admin on the server. This
was the main change in SP1 that only members of the local admin group had
the ability to use scmanager to poll the SQL2000 server to see if it was
running.

So what is SQL2005 using to see the status of SQL2005 on a server? What
permission do I need now?

Thanks

Chris

Chris
Try running
EXEC master..xp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'



"Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks John. I may try that.

I would really like the white circle against a registered server in
Management Studio to show either the White Triangle inside the Green circle
or to have the Red Square. This is on my XP SP2 workstation.

As I mentioned in SQL2000 scmanager was used to check the server status and
W2K3 SP1 blocked its use unless you were in the local admin group on the
server. We found a way of changing the permissions so we could use
scmanager.

I fancy something similar in SQL2005 and would like to know what that is.

Thanks again.

Chris

"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

Thanks John and Jasper.

The SQL Manager looks good.

Chris

"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

Thanks John and Jasper.

The SQL Manager looks good.

Chris

"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

Hi Chris

As you obviously miss SQL 2000 Then check out the customised report
Jasper has that replicates the taskpad view from Enterprise Manager
http://sqlblogcasts.com/blogs/sqldbatips/

This can be used with SQL 2005 SP2

John

"Chris Wood" wrote:

John,

I do find 2005 a lot different than 2000 but in this case I just want to
know, using SSMS, if the SQL Service is running on a server from my XP SP2
workstation.

Looks like I will have to raise an incident with MS, that's how I got 2000
SEM fixed when the servers upgraded to W2K3 SP1.

Thanks

Chris

"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

I think the problem is that Windows XP has different default firewall
settings than Server 2003 so we cannot remotely query the service through
WMI.

Here is an article on how you can properly configure your firewall settings.
http://msdn.microsoft.com/library/de...s_firewall.asp

I agree that this is a less than ideal solution. Especially in a
development or test environment where you are using/managing multiple remote
SQL Express instances on Windows XP boxes.

If this is important to you, please file this as an issue on Microsoft
Connect. http://connect.microsoft.com/SQLServer/.

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

"Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Paul,

Can you tell me what SSMS uses to see if the SQL Server service is running?
As I mentioned before it was using SCManager in SQL2000 SEM and that got
shutdown by W2K3 SP1 so that only local admins on the server could use it.

This is what we finished up doing on SQL2000.

sc sdset scmanager
DA;;LCRPRC;;;DU)(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A ;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S: (AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

This allowed non admins to at least use SCManager to query but not start or
stop a service on the remote server.

Thanks

Chris

"Paul A. Mestemaker II [MSFT]" <PaulMest (AT) online (DOT) micro-soft.com> wrote in
message news:%23ifbQovGHHA.1064 (AT) TK2MSFTNGP04 (DOT) phx.gbl...