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.
|
1 2 3 4 5 6 7 |
param ( [string]$sqlserver ) # Call this with .\filename.ps1 "servername" [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") $wmi = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" -Args $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:
- Parent
- AcceptsPause
- AcceptsStop
- Description
- DisplayName
- ErrorControl
- ExitCode
- PathName
- ProcessId
- ServiceAccount
- StartMode
- Type
- IsHadrEnabled (because I am using 11.0 SMO)
- StartupParameters
- Dependencies
- AdvancedProperties
- Urn
- Name
- Properties
- UserData
- State
With Methods
- Alter
- ChangeHadrServiceSetting (because I am using 11.0 SMO)
- ChangePassword
- Equals
- GetHashCode
- GetType
- Initialize
- Pause
- Refresh
- REsume
- SetServiceAccount (handy little guy)
- Start
- STop
- ToString
- Validate
The link to the documentation for this object is here (Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer).
Enjoy and happy scripting.
@DBAduck
Hi Ben,
You’re right, there’s no ‘ServiceAccount’ property. For some reason MS has obscured it under the name of ‘StartName’.
Here’s how I do it:
$servers = ‘Server1’, ‘Server2’
$servers | ForEach { Get-WMIObject Win32_Service -computer $_ | Where-Object { $_.Name -like ‘MSSQL*’ } | Select SystemName, Name, @{Name=”ServiceAccount”;Expression={$_.StartName}} }