SQL Query to find members of an Active Directory Security Group

By | March 30, 2023

Last updated on March 30th, 2023 at 07:01 pm

Read Time:1 Minute, 31 Second

We recently helped out with a data migration which included creating a completely new set of Active Directory security groups. They wanted all the members to have the same level of access but wanted all the new security groups to have a matching standard.

So we needed to create all the new security groups and then add all the existing members into the new ones that we had created. Considering that some of the existing security groups had 100’s of members, we needed to find a quick and simple way to copy and paste the members into the new security groups.

We found a SQL query on the internet here that worked perfectly for us, also gave us a lot of other information that we can use for future queries.

SQL query to find members of an AD security group

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

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

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

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

 FROM OPENQUERY (ADSI, ''<LDAP://' + @DC1 + '.' + @DC2 + '>;
 (&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=' + @group_dn + '));
 cn, sAMAccountName, givenName, sn, mail;subtree'')
 ORDER BY cn;'

EXEC sp_executesql @SQL

Remember to state your declare variables at the top of query.

Thanks to the original poster as we were able to then simply copy and paste the column one that is produced and paste that into our new security group!

Click to rate this post!
[Total: 0 Average: 0]

Free Subscription

If you want to be notified when we post more quality guides like this one, sign up to our free subscription service and you will receive an email when a new post is live.

Join 564 other subscribers.

No need to worry, we will not be filling your inbox with spam and you can unsubscribe anytime you like.

One thought on “SQL Query to find members of an Active Directory Security Group

  1. Avatar for Nicolas RamirezNicolas Ramirez

    Hi! Thanks for the post, it’s been really helpful! One question, how did you add the users to the other groups? Thanks again!


Leave us a message...

This site uses Akismet to reduce spam. Learn how your comment data is processed.