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();
}
Sql Server Powershell 