SQL Query - which account are the services using?

[sorehead]

Hi all,

I have around 85 remote SQL servers which all run their services off different accounts - some localsystem, others domain accounts. I really need to write a query which will tell me the domain account that runs the 3 services - if it's do-able. Can anyone help me out here please? Otherwise I have to do remote sessions on 80 machines that are all connected by ISDN and that will take serious years.........

Grateful for any help - I've tried books online and searched this forum without success (conceding that I may have searched poorly).

Thanks

Jim..

 

[fluteplr]

Since the SQL server needs to know how to log on to start itself, I seriously doubt that the information is stored in any database, since it is a chicken and the egg thing.

I would suspect it is stored in the registry. You might have better luck researching data about system services settings since that is how sql server is started by the operating system.

 

[tlbroadbent]

Fluteplr is correct. The SQL Server and Agent logon accounts are stored in the registry. If xp_regread has not been disabled in SQL Server and you have SA permissions, you can use it to read the registry.

xp_regread has 3 parameters - root key, path to the key, and key value. I believe the following example will return the SQL Server service login account - at least for SQL Server 2000.

USE Master
EXEC xp_regread
'HKEY_LOCAL_MACHINE',
'System\CurrentControlSet\Services\MSSQLServer',
'ObjectName'

You'd have to run this code on each server. I've not tested it, but it should be possible with linked servers or by using OSQL/ISQL. Hopefully, someone knows a better way to gather this info from multiple servers. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm