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
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"
/I: ignore case sensitivity.
/C: prints only the number of results. If the printed value is 1, the AD account DOEJ is a member of the group.
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..
net user/group are basic, use a full LDAP client
for complex queries (filters on OU…). This comment is highlighted because the question is recurrent. These commands
are only useful to quickly answer the question "Who is granted to access my SQL Server database ?" when no other tools are available.
An example with the
ldapsearch client: search by property
sAMAccountName and OU (
in a Microsoft Active Directory domain controller (server
% ldapsearch -h SRVDCTFR1 -p 3268 -D "mycompany\smithj" -b"OU=FR,DC=mycompany,DC=intranet" -w<mot_de_passe> "sAMAccountName=doej"