Daily Archives: September 25, 2012

Get SQL Server Service Accounts with WMI

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:

  • 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