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 } } } }