• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

PowerShell – Change SQL Server Login Password

January 14, 2014 by John Sansom 12 Comments

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

Filed Under: Administration, SQLServerCentral Syndication, SQLServerPedia Syndication Tagged With: Powershell

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy