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")
   {
    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
        }
    }

}
}

You may also like...

Leave a Reply

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