Generate User Email List For SQL Server Logins

If you’ve ever performed some impactful maintenance on a SQL Server, you probably notified users. If you’re great at documentation and already know exactly who to contact, this script isn’t for you. If you don’t have a user email list, this script will create it for you!

I used to manage 500 SQL Server instances and there was daily maintenance\changes going on constantly. I wrote this PowerShell script to automatically create an email list for me. This PowerShell script gathers the login information from an instance of SQL Server and then pulls their email address from Active Directory.

Assumptions

This script works best if you have elevated permissions on your SQL Server instance (sa, security_admin, etc…). This script also relies entirely on the Active Directory PowerShell module, so you’ll need to install that first.

Query for Logins

First step is to query SQL Server for a list of logins. There are many ways to query SQL Server with PowerShell, here I simply use the Invoke-SQLCmd CMDlet.

Query server_principals for name and type_desc, filtering out system accounts:

$Instance = 'LOCALHOST\SQL2017'

$UserQuery = "
SELECT name, type_desc FROM sys.server_principals
WHERE name NOT LIKE 'NT %'"

$Users = Invoke-SQLCmd -ServerInstance $Instance -Database 'master' -Query $UserQuery

Loop Through the Login List

With a list of our logins and their type ($Users), loop through the list, strip off the domain, and separate Windows Groups from Windows Logins:

foreach ($User in $Users) {

    $CleanName = $User.name.Split('\')[1]

    if ($User.type_desc -eq 'WINDOWS_GROUP') {

        # TODO

    } elseif ($User.type_desc -eq 'WINDOWS_LOGIN') {

        # TODO

    }
}

Capture User Emails

For the Windows Logins, let’s call the Active Directory CMDlet Get-ADUser and capture the email address. A user’s email address is typically the UserPrincipalName member returned by Get-ADUser. We’ll use a script wide variable ($Script:Emails) to save all email addresses.

$Script:Emails = @()

foreach ($User in $Users) {

    $CleanName = $User.name.Split('\')[1]

    if ($User.type_desc -eq 'WINDOWS_GROUP') {

        # TODO

    } elseif ($User.type_desc -eq 'WINDOWS_LOGIN') {
        # Access the user's email address and add it to our list.
        $Emails += (Get-ADUser $CleanName).UserPrincipalName
    }
}

Dealing with Groups

My organization has some instances of nested groups which makes processing AD groups challenging. To make matter’s worse, security will put groups and users inside other groups, so you cannot make any assumptions about group membership. Using a recursive function, we can process nested groups regardless of how many levels there are.

Adding a function Get-AllGroupMembers which takes a group as a parameter. The function will add the email address of any users in the given group. For any nested groups, they will be processed with a recursive call to Get-AllGroupMembers.

function Get-AllGroupMembers {
    param( [string] $GroupName )

    # Get the group members
    $GroupMembers = Get-ADGroupMember $GroupName | Select-Object *

    # Process each group member
    foreach ($Member in $GroupMembers) {
        # If the member is another group, recursive call
        if ($Member.objectClass -eq 'group') {
            Get-AllGroupMembers -GroupName $Member.name

        # If the member is a user, capture their email address
        } else {
            $Script:Emails += (Get-ADUser $Member.name).UserPrincipalName
        }
    }
}

Bells and Whistles

The final script is enhanced with some command line output. Any group that is processed will be printed to the command line. Nested groups will be printed on the same line for easy identification.

Lastly, the entire email list will be de-duped and copied to the clipboard. This is preferable for large email lists and can be easily pasted into your email client.

Set-Clipboard ($Script:Emails | Select-Object -Unique)

Full Script

$Instance = 'LOCALHOST\SQL2017'

$UserQuery = "
SELECT name, type_desc FROM sys.server_principals
WHERE name NOT LIKE 'NT %'"

$Users = Invoke-SQLCmd -ServerInstance $Instance -Database 'master' -Query $UserQuery

$Script:Emails = @()

function Get-AllGroupMembers {
    param( [string] $GroupName )

    Write-Host " " -ForegroundColor Cyan -NoNewline
    $GroupMembers = Get-ADGroupMember $GroupName | Select-Object *

    foreach ($Member in $GroupMembers) {
        if ($Member.objectClass -eq 'group') {
            Get-AllGroupMembers -GroupName $Member.name
        } else {
            $Script:Emails += (Get-ADUser $Member.name).UserPrincipalName
        }
    }
}

foreach ($User in $Users) {
    $CleanName = $User.name.Split('\')[1]
    if ($User.type_desc -eq 'WINDOWS_GROUP') {
        Write-Host "Processing:" -NoNewLine
        Get-AllGroupMembers -GroupName $CleanName
        Write-Host ""
    } elseif ($User.type_desc -eq 'WINDOWS_LOGIN') {
        $Emails += (Get-ADUser $CleanName).UserPrincipalName
    }
}

Set-Clipboard ($Script:Emails | Select-Object -Unique)

Write-Host "Email list has been copied to the clipboard." -ForegroundColor Cyan

Cheers.

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 )

Connecting to %s

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