Capture Sql Server Information Messages in Powershell

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.

Import-Module SqlServer -DisableNameChecking
cls
try {

$users = @('user1', 'user2')

$users | % {
$user = $_

$sqlConn = New-Object System.Data.SqlClient.SqlConnection "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=servername"
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
param(
$sender,
$event
)

Write-host $event.Message

};
$sqlConn.add_InfoMessage($handler);
$sqlConn.FireInfoMessageEventOnUserErrors = $true;
$sqlConn.Open();
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand("EXEC sp_help_revlogin '$user', NULL", $sqlConn);
$sqlcmd.CommandType = [System.Data.CommandType]::Text
[void]$sqlCmd.ExecuteNonQuery();
}

}
catch {
$_ | fl -Force
}
finally {
$sqlConn.Close();
}

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

%d bloggers like this: