How to list members of an Active directory group defined in Microsoft SQL Server

Introduction

Very often, project managers or business analysts, who do not have the necessary tools or by lack of knowledge, ask directly the database administrators for the list of accounts belonging to a Microsoft Active Directory group defined in Microsoft SQL Server.

Here is a list of useful commands (net group, net user) retrieving the members of a group without disturbing Active Directory Domain Controller (AD) administrators.

In this article, the group <mydomain>\ fr.trading.rog is defined in a SQL Server 2008 R2 server (SRVWINFR122). This group has been granted read-only rights in the trading database through the SQL Server system role db_datareader :

CREATE LOGIN [<mondomaine>\fr.trading.rog]
FROM WINDOWS WITH DEFAULT_DATABASE=[trading],
DEFAULT_LANGUAGE=[us_english]
GO

use [trading]
GO<

CREATE USER [<mondomaine>\fr.trading.rog]
FOR LOGIN [<mondomaine>\fr.trading.rog]
GO

execute sp_addrolemember 'db_datareader',[<mondomaine>\fr.trading.rog]
GO

List the members of an AD group with the net group command

In a DOS command prompt, the command net group retrieves all the members in a Microsoft AD group. To find the members of the AD group fr.trading.org in the current domain :

net group /domain "fr.trading.rog"
Group name    fr.trading.rog
Comment

Members

------------------------------------------------------------------
DOEJ                 MARTINS                 DURANDP

The command completed successfully.

All AD accounts defined in the group are returned by the command. In this example, 3 accounts are defined.

Details of an AD group member with the net user command

To go further, still in a DOS command prompt, the command net user returns the details for an AD account (first name, last name…). To get the details about the account DOEJ defined in the group "fr.trading.org" :

net user /domain "DOEJ"
User name                                      DOEJ
Full Name                                      DOE John
Comment                                        Trader
User's comment
Country code                                   (null)
Account active                                 Yes
Account expires                                Never
…

Finding if an AD account is a member of a group with the net group and find commands

By combining the net group and find commands in a DOS command prompt, it is possible to determine very quickly whether the account DOEJ account is indeed defined in the "fr.trading.rog" group :

net group /domain "fr.trading.rog" | find /I /C "DOEJ"
1

• Option /I : ignore case sensitivity.
• Option /C : prints only the number of results. If the printed value is 1, the AD account DOEJ is a member of the group.

Conclusion

The net group and net user commands retrieve quickly the list and the details for AD accounts defined in a group used within SQL Server engine. These command lines have 3 valuable benefits :

• They are available on the workstations and the servers.
• They can be used easily by advanced users who do not have complementary tools for querying any LDAP server, like Microsoft Active Directory Server.
• They avoid disturbing domain controllers administrators.

Other tools are available (dsquery, dsget, PowerShell, ldapsearch…) but with 3 inconvenients :

• Additional components have to be installed (Windows SDK par exemple).
• Command lines are a bit more complex, even for advanced users (especially PowerShell).
• Some methods are only available on servers and not on Workstations..

An example with the ldapsearch client: search by property sAMAccountName and OU ("OU=FR,DC=mycompany,DC=intranet") in a Microsoft Active Directory domain controller (server SRVDCTFR1, port 3268).

% ldapsearch -h SRVDCTFR1 -p 3268 -D "mycompany\smithj"
        -b"OU=FR,DC=mycompany,DC=intranet" -w<mot_de_passe> "sAMAccountName=doej"