SQL Query to find members of an Active Directory Security Group

sql2016

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 @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, ''<LDAP://' + @DC1 + '.' + @DC2 + '>;
 (&(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

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!

Free Subscription!Subscribe to our site to receive updates via email!

Enter your email address below and click the Subscribe button to receive email notifications about new and exciting downloads available through our website at https://www.techygeekshome.com

You will also receive notifications about new technical guides, latest news and MSI installer downloads that are available through our blog here at https://blog.techygeekshome.info 

We will not spam you and we will not pass on any of your details to anyone else. We tend to post new content once or twice a week.

Join 281 other subscribers

You can unsubscribe any time you like.

About A.J. Armstrong

Founder of TechyGeeksHome and Head Editor for over eight years! IT expert in multiple areas for over 20 years. Sharing experience and knowledge whenever possible! Making IT Happen.

View all posts by A.J. Armstrong