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.

[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
Advertisements

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: