PowerShell – Change SQL Server Login Password

Here’s a quick post detailing a PowerShell script that can be used to change the password for a SQL Server Login.
Regular readers know that I practice the philosophy of Automate Everything and DBA administrative tasks are no exception. I don’t want to be doing the same task by hand twice, if I can help it.
A requirement came up recently where I had the need to change a number of SQL Server Logins on multiple SQL Server instances. That’s just the sort of exciting work that PowerShell can be used for.
Below you’ll find a script that I threw together in order to complete the task. It accepts a list of server/instance names as a text file, and then you enter the SQL Server Login name and password as parameters on the command line. You don’t want to be storing those in a text file right!

# Date:         11/01/14

# Author:       John Sansom

# Description:  PS script to change a SQL Login password for a provided server list.

#           The script accepts an input file of server names.

# Version:  1.0

#

# Example Execution: .\Change_SQLLoginPassword.ps1 .\ServerNameList.txt SQLLogin Password

param([String]$serverListPath, [String]$login, [String]$password)

#Load the input file into an Object array

$ServerNameList = get-content -path $serverListPath

#Load the SQL Server SMO Assemly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object

$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

#For each server in the array do the following..

foreach($ServerName in $ServerNameList)

{

Try

{

$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"

Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline

$objSQLConnection.Open() | Out-Null

Write-Host "Success."

$objSQLConnection.Close()

}

Catch

{

Write-Host -BackgroundColor Red -ForegroundColor White "Fail"

$errText =  $Error[0].ToString()

if ($errText.Contains("network-related"))

{Write-Host "Connection Error. Check server name, port, firewall."}

Write-Host $errText

continue

}

#Create a new SMO instance for this $ServerName

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

#Find the SQL Server Login and Change the Password

$SQLUser = $srv.Logins | ? {$_.Name -eq "$login"};

$SQLUser.ChangePassword($password);

$SQLUser.PasswordPolicyEnforced = 1;

$SQLUser.Alter();

$SQLUser.Refresh();

Write-Host "Password for Login:'$login' changed sucessfully on server:'$ServerName' "

}