Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sansom - SQL Server DBA in the UK

John Sansom (Blog | Twitter) is a Microsoft Certified Master (MCM) of SQL Server and publisher of the free SQL community ebook DBA JumpStart, an inspiring collection of advice for Data Professionals, written by 20 SQL Server experts. Awarded the Microsoft Community Contributor(MCC) award, John is a prolific blogger and can be found regularly writing about SQL Server and Professional Development over at www.johnsansom.com.

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

Comments

Leave a comment on the original post [www.johnsansom.com, opens in a new window]

Loading comments...