Domain Security Groups – Who is in the group?

As we all know, user access is one of the tasks that DBA’s are responsible for in their environment.  And if we are following best practices and want to make our lives easier, we should be handling user access with domain security groups, rather than individual domain user accounts or SQL logins.  However, from time to time, we will need to know what users are in a specific domain security group.  How can we do that?

T-SQL

  • xp_logininfo(http://msdn.microsoft.com/en-us/library/ms190369.aspx) – This extended stored procedure can be used along with the “members” option to return the next level of users in a group.  However, if there are multiple nested groups, this will not return the next level groups, only users.
</pre>
EXEC sys.xp_logininfo @acctname = 'DOMAIN\GroupName', @option = 'members'
<pre>

PowerShell

</pre>
$strFilter = "(&(objectCategory=Group)(Name=[GroupName]))"

$objDomain = <strong>New-Object</strong> System.DirectoryServices.DirectoryEntry

$objSearcher = <strong>New-Object</strong> System.DirectoryServices.DirectorySearcher

$objSearcher.SearchRoot = $objDomain

$objSearcher.PageSize = 1000

$objSearcher.Filter = $strFilter

$objSearcher.SearchScope = "Subtree"

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)

{

$objItem = $objResult.GetDirectoryEntry()

$objItem.member

}
<pre>

So, as always there are many ways that things can be done.  and I have briefly listed two different ways that a person can find out who is in a domain security group.

Advertisements
This entry was posted in PowerShell, Security, TSQL and tagged . Bookmark the permalink.

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