List all SQL Server-Related Service Accounts using WMI under PowerShell

Ever wanted to know which service accounts SQL Server is using?

Fast PowerShell WMI Method(doesn’t work with boxes running Windows 2000)

1. Make a text file containing all the machine names of your SQL Servers – don’t worry about multiple instance names or virtual names, just put the machine names, one per line. This file will be used to populate the service account info once for each server listed.

2. Create a text file with the following, and save it as get-sql-svcaccounts-wmi.ps1 to be run in PowerShell.

$erroractionpreference = "SilentlyContinue"
foreach ($server in get-content("2009-10-27-sql-servers.txt"))
     {
     
     $class = Get-WmiObject -computername $server -namespace root\Microsoft\SqlServer\ComputerManagement -class SQLService
     "`n" + $server + ":"
     foreach ($instance in $class) {$instance.HostName + ", " + $instance.ServiceName + ", " + $instance.StartName}
     }

Why silently continue in case of error? Well that’s for those lovely Windows 2000 boxes that are _still_ on your network.

Here’s a screenshot:

Slow PSSERVICE + PowerShell Method(works with boxes running Windows 2000 and later OS versions of Windows)

Here’s a PSSERVICE.EXE + PowerShell script that will work with Windows 2000 boxes. The script builds a PSSERVICE string & runs the string using Invoke-Expression and passing the prefix of each service name.

1. Make a text file containing all the machine names of your SQL Servers.

2. Create a text file with the following, and save it as get-sql-svcaccounts.ps1 to be run in PowerShell.

foreach ($server in get-content("2009-10-27-sql-servers.txt"))
     {
     $exp1="psservice \\"
     $exp2=" security MSSQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     
     #debug
     #echo $cmd_line
     
     "`n" + $server + ":"
     Invoke-Expression $cmd_line
     #part 2
     $exp2=" security MSDts | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 3
     $exp2=" security MSFTE | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 4
     $exp2=" security SQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 5
     $exp2=" security Report | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 6
     $exp2=" security MSOLAP | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
          
     }

Yes, I realize the above could be much shorter – if you rewrite it I’ll edit this post and give you credit.

Here’s a screenshot of the PSSERVICE approach: