Change SQL Server Authentication login password
We have numerous applications using SQL Login accounts . Simple powershell script saves time for rotating passwords for SQL Logins.
# Date: 07/12/2016
# Author: Raju Venkataraman
# Description: PS script to change a SQL Login password for a provided server list.
# Version: 1.0
# Example Execution: Change-SQLLoginPassword -serverList ("servername") -login "uname" -password "currentpassword" -newpassword "newpassword" -logintype "SQL"
function Change-SQLLoginPassword([String[]]$serverList, [String]$login, [String]$password , [String]$newpassword , [String] $logintype)
{
## Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
#Load the SQL Server SMO Assembly
[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 $serverList)
{
if ($logintype -eq "SQL")
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName; User ID = $login; Password = $password;"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
if ($objSQLConnection)
{
$sql = "ALTER LOGIN [$login] WITH CHECK_POLICY = OFF
ALTER LOGIN [$login] WITH PASSWORD = '$newpassword' UNLOCK"
$cmd = New-Object system.data.sqlclient.sqlcommand($null, $objSQLConnection)
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery() | Out-Null
$cmd.Dispose()
Write-Host "Password for Login:'$login' changed successfully on server:'$ServerName' "
}
$objSQLConnection.Close()
$objSQLConnection.Dispose()
}
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
}
}
}
}