Change SQL Server Authentication login password

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")
            $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$null, $objSQLConnection)
				$cmd.CommandText = $sql
				$cmd.ExecuteNonQuery() | Out-Null

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

            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


Leave a Reply

Your email address will not be published. Required fields are marked *