Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    CA, USA
    Posts
    21

    Question Unanswered: How to get service accounts for 150 servers

    Hi Everyone.

    I have 150 SQL servers (2000 MSDE). They all run using various domain accounts as their service logins. Is there an automated way to find out those service logins? Maybe a query I could run on each server?

    I really do not want to go to each of those 150 servers and look at their properties manualy!

    Any help would be greatly appreciated! Thank you.
    Не дают котенку сала,
    Не дают и колбасы
    У него вся шерсть отпала,
    Повылазили усы...

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Something around the master.dbo.sysusers and/or master.dbo.syslogins
    table(s) ?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you want the startname value from this script.

    -PatP

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    I think that you want the startname value from this script.

    -PatP
    Yeah, I would think that WMI would be your best bet. Dump the list of servers into a text file (or XML). Open that file using the File system object and spin through (using oFile.ReadLine). Then feed the string value (the computer name) into a function that returns the name of of the service account you are looking for. Dump that into a separate text file (or a database).

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2003
    Location
    CA, USA
    Posts
    21
    Thank you everyone for responding. I got an answer at experts-exchange. The following query will return the service account:

    declare @rc int,
    @dir nvarchar(4000)
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQLServer\',N'ObjectName', @dir output, 'no_output'
    select @dir

    I've modified it put the result in the central table and then ran it through a SQL loop. Worked perfectly!

    WMI would have worked as well, but this solution took just 10 min to implement.

    P.S. Had no idea that something like xp_instance_regread existed. Is too much to hope that sql server 2005 will have better documentation? :-)
    Last edited by pushistik; 08-22-06 at 15:26.
    Не дают котенку сала,
    Не дают и колбасы
    У него вся шерсть отпала,
    Повылазили усы...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    CA, USA
    Posts
    21
    Quote Originally Posted by Pat Phelan
    That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

    -PatP
    Good point, Pat. For named instances the command will need to be modified:

    declare @rc int,
    @dir nvarchar(4000)
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQL$Your_Instance_Name\',N'ObjectName', @dir output, 'no_output'
    select @dir
    Не дают котенку сала,
    Не дают и колбасы
    У него вся шерсть отпала,
    Повылазили усы...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •