Update sql server operators (redux)

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.


$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
$outputs | SELECT ServerName, OperatorName, OldEmailAddress, NewEmailAddress | Export-Csv -Path $reportPath -NoTypeInformation -Force

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.