I have a version of this already on here that uses SQLPSX, but since that’s not being updated anymore and that you may not always have SQLPSX available, here is a version that does not use SQLPSX. I’ve more or less stopped using SQLPSX, but I do miss it at times.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$outputs = @();
$newEmailAddress = 'NewEmailAddress@me.com'
$oldEmailAddress = 'OldEmailAddress@me.com'
$reportPath = 'c:\UpdatedOperators.csv'
$servers = gc -Path 'C:\Servers.txt'
$servers | %{
$srvName = $_
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$srv.JobServer.Operators | where{$_.EmailAddress -eq $oldEmailAddress} | %{
$output = New-Object -TypeName PSObject -Property @{
ServerName = $srvName
OperatorName = $_.Name
OldEmailAddress = $_.EmailAddress
NewEmailAddress = $newEmailAddress
}
$outputs += $output
$_.EmailAddress = $newEmailAddress
$_.Alter();
}
}
$outputs | SELECT ServerName, OperatorName, OldEmailAddress, NewEmailAddress | Export-Csv -Path $reportPath -NoTypeInformation -Force
Sql Server Powershell 
Leave a comment