Get Active Directory Group Members Using Powershell and SQL Server

Using Active Directory Groups (AD) for SQL Server authentication makes managing access permissions easy. Instead of managing individual login permissions the DBA can recommend using some AD groups that can contain many users and the login and permissions are assigned only one time.

I have limited knowledge of Active Directory so these examples will be short and focused on getting the information I need to automate SQL Server tasks.

Active Directory Group Members in SQL Server

SQL Server has some very useful extended stored procedures that provide more extensible functions outside of Database management. One of these is “xp_logininfo“, a Procedure that returns the logins configured and can include the members of an Active Directory group as well as groups within the group too.

To return the Logins currently configured using syslogins and xp_logininfo:

EXEC xp_logininfo 
SELECT * FROM syslogins WHERE isntgroup = 1

Return just one login user or group information:

EXEC xp_logininfo 'mydomain\superusername'
EXEC xp_logininfo 'mydomain\UserGroupNumber1'

Return the AD Groups member logins:

EXEC xp_logininfo 'mydomain\UserGroupNumber1', 'members'

The procedure is specific to SQL Server so the login needs to exists on the SQL Instance for the procedure to work. The catch is if there are more embedded groups within groups you may not be able to get the members level unless using Powershell and talking directly to Active Directory.

Install Remote Server Administrative Tools (RSAT)

In order to view the AD your Windows Server 2016 server you may need to install the RSAT tools. Your company server build may already include RSAT but if it is not installed you can use:

Import-module -Name ActiveDirectory

Install-WindowsFeature -IncludeAllSubFeature RSAT

The installation for a Windows 10 Client is different and requires a connection to the internet:

Import-Module -Name ServerManager

Get-WindowsCapability -Online | Where-Object {$_.Name -like "Rsat*" -AND $_.State -eq "NotPresent"} | Add-WindowsCapability -Online 

If your running Windows 2012 try this:

Import-Module -Name ServerManager

Add-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature

AD Groups Using Powershell

Using Powershell you can take advantage of some powerful and fast cmdlets called Get-ADUser, Get-ADGroupMember and Get-ADOrganizationalUnit to query Active Directory. Use Get-ADOrganizationalunit to find a list of the groups, users and other objects available using Powershell.

# You must be logged into the domain
Get-ADGroup  -Filter *
Get-ADOrganizationalUnit -Filter *

# get the group based on the name such as SQL
Get-ADGroup  -Filter * | Where-Object {$_.Name -like "*sql*" } 


# Get only the Domain and OU, to be used in the next examples
Get-ADGroup  -Filter * | Select-Object DistinguishedName
Get-ADGroup  -Filter * | Where-Object {$_.DistinguishedName -like "*user*" } 

# Get only the OU's that have SQL in the name
Get-ADOrganizationalUnit -Filter * | Select-Object DistinguishedName | Where-Object {$_.DistinguishedName -like "*sql*" }

# Only results with "users"
Get-ADGroup  -Filter *  | Where-Object {$_.DistinguishedName -like '*users*'}

# only groups
Get-ADGroup  -Filter *  | Where-Object {$_.ObjectClass -eq 'group'}

You can now see the objects and fields that we can Filter for. AD data can be large depending on your Organization, to minimize how much data and how long the PS script runs we can define a -searchbase (or container ) to start from. Use the filter to reduce the number of OU’s which can help with speed.

Notice in the previous results we have a “DistinguishedName” field and we used it to filter too?

The order is important in the -SearchBase so I am copying these as a string to test collecting objects from a single container.

Get-ADOrganizationalUnit -SearchBase "OU=Sample,OU=Servers,DC=ad,DC=mydomain,DC=net" -Filter *
Get-ADOrganizationalUnit -SearchBase "OU=Users,DC=ad,DC=mydomain,DC=net" -Filter *

# Get the Name for all 
(Get-ADGroup  -Filter * -SearchBase "OU=Production,DC=ad,DC=mydomain,DC=net"  | Where-Object {$_.ObjectClass -eq 'group'}).Name

(Get-ADGroup  -Filter * -SearchBase "OU=Production,DC=ad,DC=mydomain,DC=net"  | Where-Object {$_.ObjectClass -eq 'group'}).SamAccountName

Your domain and logical naming conventions will be different but the objects returned contain similar fields.

Using Get-ADGroupMember To Get Logins

With the group name we can use the get-adgroupmember cmdlet to list everyone who is a member.

# get members
Get-ADGroupMember "Mygroup-Name-Users-Production"

# Get Just the names
(Get-ADGroupMember "Mygroup-Name-Users-Production").Name

# Get the login
(Get-ADGroupMember "Mygroup-Name-Users-Production").SamAccountName

Using Get-ADUser

The last cmdlet we want to review is the Get-ADUser where we can query AD for the User information or Login if were looking for SQL logins and groups.

Get-ADUser -Filter *
Get-ADUser -Filter * -SearchBase "OU=Users,DC=ad,DC=mydomain,DC=net"
Get-ADUser -Filter * -SearchBase "OU=Users,DC=ad,DC=mydomain,DC=net" | Where-Object {$_.objectclass -eq "user" }


# find your AD login; notice the quotes
Get-ADUser -Filter "samaccountname -eq 'username'"

This last one probably should have been first. Looking up your login on the domain can reveal some useful data such as the DistinguishedName that you can copy and use in our Get-ADgroup filter:

Get-ADGroup  -Filter * -Searchbase "OU=PPC,DC=Sample,DC=ad,DC=MyDomain,DC=net"

# Just get the group names
(Get-ADGroup  -Filter * -Searchbase "OU=ITD,DC=cc,DC=ad,DC=cchs,DC=net").Name

---