Welcome to Day 26 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
If you utilize the sa login to perform admin chores (instead of selected windows accounts with windows authentication only), then you should periodically change the sa passwords. And these passwords should be different on each server. So, how do you want to do this: log in to each server, navigate to the server logins, and change the sa password… or run a PowerShell script to connect to each server and change it according to a list?
That’s what I thought. Let’s make a PowerShell script.
We’ll start off by creating a delimited text file. At your PowerShell prompt, enter:
|
1 |
notepad $ENV:TEMP\saPwdList.csv |
In the file that opens up, on the first line put ServerName and Pwd, separated by the delimiter of your choice (ie. “|”). On subsequent lines, enter each Server\Instance name, the chosen delimiter, and the sa password to use on that server.
Finally, run the following script to change all of those passwords:
|
1 2 3 4 5 6 7 8 9 10 |
$Items = Import-CSV $ENV:TEMP\saPwdList.csv -Delimiter ","| #Use the chosen delimiter ForEach ($Item in $Items) { $Instance = $Item.ServerName $Pwd = $Item.Pwd $Server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance $Server.Logins.Item('sa').ChangePassword($Pwd) $Server.Logins.Item('sa').Alter() } |
This script could easily be modified to handle any login on multiple servers:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
param( [string]$LoginName ) $Items = Import-CSV $ENV:TEMP\ServerList.csv -Delimiter | #Use the chosen delimiter ForEach ($Item in $Items) { $Instance = $Item.ServerName $Pwd = $Item.Pwd $Server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance $Login = $Server.Logins[$LoginName] if (($Login)) #Check to see if the login exists { $Login.ChangePassword($Pwd) $Login.Alter() } } |
This script checks to see if the login exists on the server, and then changes the login’s password to the value specified. Both the servers and passwords are read in from the file.