Today I was tasked with retrieving the SQL Server Service accounts to validate them in Active Directory. I wanted a simple way to get them and show the account names for each SQL Server service. I knew that you could get the service in PowerShell with Get-Service. I also new that PowerShell could get the service with Get-WmiObject –class Win32_Service. But neither one had the ServiceAccount property.
So I turned to WMI under SMO with PowerShell. The script below has the details of a script I named GetSqlServices_WMI with a parameter of $sqlserver.
param ( [string]$sqlserver ) # Call this with .\filename.ps1 "servername" [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") $wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sqlserver $wmi.Services | Select DisplayName, Type, StartMode, ServiceState, ServiceAccount | ft -auto
The call is in the code. The results will have DisplayName, Type, StartMode, ServiceState and ServiceAccount listed in a table with column headers.
Great way to interrogate the Services from SQL Server.
Available Properties to you are as follows:
- IsHadrEnabled (because I am using 11.0 SMO)
- ChangeHadrServiceSetting (because I am using 11.0 SMO)
- SetServiceAccount (handy little guy)
The link to the documentation for this object is here (Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer).
Enjoy and happy scripting.