SQL Query Active Directory: Finding Security Group Members

During a data migration project, we needed to build a fresh set of Active Directory security groups and populate each new group with the same membership as an existing one — some of which had hundreds of members. Rather than copying members by hand, this SQL query pulls the full membership list of an AD security group directly via a linked ADSI query, ready to copy into the new group.

The query

DECLARE @group NVARCHAR(128) = 'AD GroupName'
DECLARE @DC1 NVARCHAR(128) = 'domain'
DECLARE @DC2 NVARCHAR(128) = 'com'

DECLARE @SQL NVARCHAR(MAX)
DECLARE @group_dn NVARCHAR(512)
DECLARE @result TABLE(name NVARCHAR(512))

SET @SQL =
'SELECT distinguishedName
FROM OPENQUERY
(ADSI,''SELECT cn, distinguishedName, dc
FROM ''''LDAP://DC=' + @DC1 + ',DC=' + @DC2 + '''''
WHERE objectCategory = ''''group'''' AND cn = ''''' + @group + ''''''')'

--PRINT @SQL
INSERT @result(name)
EXEC sp_executesql @SQL
SELECT @group_dn = name FROM @result

SET @SQL =
'SELECT *
FROM OPENQUERY (ADSI, '';
(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=' + @group_dn + '));
cn, sAMAccountName, givenName, sn, mail;subtree'')
ORDER BY cn;'

--PRINT @SQL
EXEC sp_executesql @SQL

Using it

Set the three variables at the top: @group is the exact cn of the security group you want to query, and @DC1/@DC2 are your domain’s two components (e.g. for contoso.com, that’s DC1 = 'contoso', DC2 = 'com'). This requires that your SQL Server instance has an ADSI linked server configured — this query relies on OPENQUERY(ADSI, ...), so if you haven’t got an ADSI linked server set up already, that needs configuring first.

The query uses the 1.2.840.113556.1.4.1941 LDAP matching rule, which resolves nested group membership transitively — so it correctly returns members of nested sub-groups too, not just direct members. Run it, and the result set gives you name, sAMAccountName, and email for every member, ready to paste straight into your new group’s membership.

Credit to the original poster on the Microsoft SQL Server forum this technique came from — it saved us a lot of manual copying.

Resources

• Active Directory
• in this Microsoft forum thread