PowerShell – Email All Users Connected (Logged In) To A SQL Server Instance

As a DBA, when I have to do an emergency restart of a SQL Server instance or the host itself, the first thing I do is to check who is connected so that I can notify them (at least the ACTIVE users). We do have a system where we keep the app/database/owner information but notifying who is connected right now is more relevant.

Would it not be nice if I could say what the subject/body of the email should be and my function emails all the connected users with a message like “Database/Instance xyz is going down in 10 minutes”? It is simpler than you think!

Prerequisites

Although it is pretty straight-forward, there are a few prerequisites

  • SQLPS or SQLServer PowerShell module must be installed/present
  • Active Directory PowerShell Module is needed to get email information for logins
  • Access to a SMTP server is required (does not have to be on your host)
  • Firewall rules should not prevent SMTP mailing

Code comments and code

WordPress has an issue with PowerShell block comments. So, below, I am separating the code block comments from the actual code.

#
#######################
#
.SYNOPSIS
    Emails all users connected to a given SQL Instance with given subject/body

.DESCRIPTION
    When you are about to restart a SQL Instance or host, you might want to notify
        users connected to the database. This function should come in handy to do so

.INPUTS
    SMTP/SQL host info + email details

.OUTPUTS
    Sends an email
.EXAMPLE
#This is a simple example that sends out the default message
$serverInstance = 'SQLHOST\SQLInstance'

Send-MailToConnectedUsers `
-ServerInstance $serverInstance
.EXAMPLE
        #This example customizes what will be sent out
$serverInstance = 'SQLHOST\SQLInstance'
        $smtpServer = 'YourSMTPServer'
        $emailFrom = 'YourEmail@YourCompany.com'
        $emailSubject = "[$serverInstance] - Going down in 10 minutes"
        $emailBody = "SQL Server instance [$ServerInstance] needs emergency maintenance and will be unavailable starting in 10 minutes for the next 1/2 hour. Please save your work."

        Send-MailToConnectedUsers `
            -ServerInstance $serverInstance `
            -SmtpServer $smtpServer `
            -From $emailFrom `
            -Subject $emailSubject `
            -Body $emailBody 

.NOTES 

Version History
    v1.0  - Jul 31, 2017. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    sqljana.wordpress.com
#
#

The actual code is below. If you will use the default values of the parameters, please make sure that the right values are filled in

function Send-MailToConnectedUsers
{
    [CmdletBinding()]
    param( 

        [Parameter(Mandatory=$true)]
        [string] $ServerInstance,

        [Parameter(Mandatory=$false)]
        [string] $SmtpServer = 'MySMTPServer',

        [Parameter(Mandatory=$false)]
        [string] $From = (Get-ADUser ($env:UserName) -properties mail).mail,

        [Parameter(Mandatory=$false)]
        [string] $Subject = "[$ServerInstance] - Going down for emergency maintenance",

        [Parameter(Mandatory=$false)]
        [string] $Body = "SQL Server instance [$ServerInstance] needs emergency maintenance and will be unavailable starting in 10 minutes for the next 1/2 hour. Please save your work.",

        [Parameter(Mandatory=$false)]
        [switch] $BodyAsHTML = $false
    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]"  

    try
    {    

        $stepName = "[$fn]: Get sp_who results"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        $rslt = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'sp_who' -Database 'master'

        $stepName = "[$fn]: Get the distinct users (only Windows logins with domain suffix like MS\USERNAME)"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        $users = $rslt |
                    Select-Object -ExpandProperty loginame |
                    Select-Object -Unique |
                    Where-Object {$_.Contains('\')}

        $stepName = "[$fn]: Create a email To user list"
        #---------------------------------------------------------------
        Write-Verbose $stepName
        
        $userEmails = ''
        foreach($user in $users)
        {
            #Remove the domain prefix that is in "MS\UserName" for example
            $userNoPrefix = $user.Split('\')[1]

            if ($userNoPrefix)
            {
                $userEmail = (Get-ADUser $userNoPrefix -properties mail).mail

                if ($userEmail)
                {
                    $userEmails = $userEmails + $userEmail + ';'
                }
            }
        }

        if ($userEmails.Replace(';','').Trim().Length -eq 0)
        {
            Throw 'There are no connected users (or) all connected users are not in Active Directory and hence do not have an associated email ID!'
        }

        $stepName = "[$fn]: Send an email to connected users!"
        #---------------------------------------------------------------
        Write-Verbose $stepName        
        
        #Send-MailMessageViaSQLServer -InstanceName 'prod-sqlcms' `

        Send-MailMessage `
            -From $From `
            -Body $Body `
            -Subject $Subject `
            -To ($userEmails.Split(';',[System.StringSplitOptions]::RemoveEmptyEntries)) `
            -SmtpServer $SmtpServer `
            -BodyAsHtml: $BodyAsHTML
 
 
    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to email connected users. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}  

The only required parameter is the SQL Server instance name. The rest are optional assuming you want them at their current defaults!

Sending pretty (HTML formatted) emails

If wanted to send a more professional looking email, you can create the HTML for it which becomes the Body parameter value and the switch BodyAsHTML should be set to $true. This will send out pretty emails!

Troubleshooting

If you run into errors, please make sure you are able to use the Send-MailMessage cmdlet in a stand-alone fashion to ensure that emailing works.

Advertisements

One thought on “PowerShell – Email All Users Connected (Logged In) To A SQL Server Instance

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