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 (@SQLBrit) is a Technology Lead with the database team at Expedia, Inc. providing consulting services and support for one of the worlds largest SQL Server environments. Awarded the Microsoft Community Contributor Award (MCC) John can be found regularly blogging about Being a DBA 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...