Resolve Sql Server Windows Login Groups using Powershell

Quick script to resolve sql server windows groups to show who is in said groups.  This uses SQLPSX and Quests’ QAD cmdlets (which is apparently owned by dell now?).

	[string]$serverInstance = {throw "You must enter a sql server instance name"}

Import-Module SqlServer

if ( (Get-PSSnapin -Name quest.activeroles.admanagement -ErrorAction SilentlyContinue) -eq $null )
    Add-PsSnapin quest.activeroles.admanagement

$outputs = @();
$srv = Get-SqlServer -sqlserver $serverInstance
$logins = $srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup}

foreach($login in $logins)
	$qadUsers = Get-QADGroupMember $login.Name | select Name, NTAccountName # | sort Name | Format-Table -AutoSize
	foreach($user in $qadUsers)
		$output = New-Object -TypeName PSObject -Property @{
			ServerName = $srv
			GroupName = $login.Name
			Name = $user.Name
			AccountName = $user.NTAccountName
		$outputs += $output	
$outputs | SELECT ServerName, GroupName, Name, AccountName #| Format-Table -AutoSize

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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.