Author Archives: John Perry

I just wanted a wrapper for Get-ADGroup and Get-ADGroupMember to return all members of a group.

function Get-ADGroupMembers {
	<#
		.SYNOPSIS
			For any Get-ADGroup Get-ADGroupMember's Get-ADUser properties and output our PSObject

		.DESCRIPTION
			For any AD group return a list of members including members of subgroups.

		.PARAMETER  Group
			The AD Group name to find members for (without the DOMAIN).
 
		.EXAMPLE
			Get-ADGroupMembers -Group "SQL DBA"

		.EXAMPLE
			$Group = "SQL DBA"
			$Output = Get-ADGroupMembers -Group $Group
			$Output |Sort-Object LastName |Select-Object ObjectClass, GroupName, LastName, GivenName, SamAccountName, Name |ft -AutoSize
			Write-Host ("{0} Members found in AD group: {1}" -f $Output.Count, $Group)
			
		.INPUTS
			System.String 

		.OUTPUTS
			PSObject

		.NOTES
			20170113 Created

 

	#>
	[CmdletBinding()] 
	param(
		[Parameter(Position=0, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$Group 
	)
	try {
				
		#
		## Get all 
		#  
		
		$Groups = Get-ADGroup -Identity $Group 	 
		$output = ForEach ($g in $groups)  {
			$results = Get-ADGroupMember -Identity $g.name -Recursive | Get-ADUser -Properties displayname, objectclass, name 

			ForEach ($r in $results){
				New-Object PSObject -Property @{
				    GroupName = $g.Name
				    Username = $r.Name
				    ObjectClass = $r.ObjectClass
				    Name = $r.Name
					GivenName = $r.GivenName
					LastName = $r.Surname
					Enabled = $r.Enabled
					SamAccountName = $r.SamAccountName 
				 }
			}
		} 
	  Write-Output $output
	}
	catch {
		throw
	}
}

I use this to retrieve all SQL sysadmins on a server, and drill down, if any are Active Directory groups, to the individuals.

$SQLInstance = "SQLDEV";
$DomainFilter = "MYCOMPANYDOMAIN"

$Server = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstance;
$SQLLogins = $Server.Logins;

$sysadmins = $null;
$sysadmins = foreach($sysadmin in $SQLLogins)  {
	foreach($role in $sysadmin.ListMembers()) {
		if($role -match 'sysadmin') {
			Write-Verbose "sysadmin found: $($sysadmin.Name)" 
			$sysadmin | Select-Object `
				@{label = "SQLServer"; Expression = {$SQLInstance}}, `
				@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
				Name, LoginType, CreateDate, DateLastModified;
			$sysadmin |?{$_.LoginType -eq 'WindowsGroup' -and $_.Name -match '$DomainFilter'} |%{ 
				Get-ADGroupMembers -Group $_.Name.Replace("$DomainFilter\",'') |Sort-Object LastName | Select-Object `
						@{label = "SQLServer"; Expression = {$SQLInstance}}, `
						@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
						Name,@{label = "LoginType"; Expression = {'GroupUser'}}
	 
	}
		};
	};
};

$sysadmins | ft -AutoSize