Managing multiple SQL Servers can be a challenging task, especially when it comes to changing the service account or password. While there are tools available for this purpose, some database administrators prefer to automate the process using scripts. In this article, we will explore how to change the service account in SQL Server using WMI/SMO.

Before we dive into the script, let’s understand the importance of using the right tools for the job. Changing the service account or password directly from the SQL Server Configuration Manager is the recommended approach. However, there may be scenarios where automating the process becomes necessary, such as managing a large number of SQL Servers.

The script provided below demonstrates how to change the service account for all SQL instances on a machine using WMI (Windows Management Instrumentation) and SMO (SQL Server Management Objects).

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | out-null
$Server = 'SQL16NodeB'
$UserName='SQLAuthority\SQLSvc'
$Password='Sysadmin@1234'
$SMO = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $Server
$Service = $SMO.Services | where {$_.type -like 'SQLServer'}
Write-Host 'Properties before Change'
$Service | select name, ServiceAccount, DisplayName, StartMode  | Format-Table
$Service.SetServiceAccount($UserName, $Password)
Write-Host 'Properties after Change'
$Service | select name, ServiceAccount, DisplayName, StartMode | Format-Table

Before executing the script, make sure to modify the variables at the top according to your environment. The $Server variable should contain the name of the SQL Server instance, $UserName should contain the desired service account, and $Password should contain the corresponding password.

Once the script is executed, it will display the properties of the service account before and after the change. The script filters out SQL Server services using the condition where {$_.type -like 'SQLServer'}, ensuring that only SQL Server accounts are modified.

It’s important to note that this script is just one way to change the service account in SQL Server. There may be other methods or tools available depending on your specific requirements. However, this script serves as a foundation for standard scripts related to SQL using SMO and WMI.

How do you change the service account or password in your environment? Share your thoughts and experiences in the comments below!