December 6, 2013 at 12:40 pm
The following "borrowed" query I found on the internet works great for finding all Users in all Groups. However, it does not show nested security groups. Can someone assist me in how to find all groups that are nested within other groups?
Here is the code I'm using to find all users in all groups:
create table #MemberOfGroups(
groupNm varchar(400),
sAMAccountName varchar(400),
displayName varchar(400))
SET NOCOUNT ON
declare @t varchar(100),@t2 varchar(1000), @ot varchar (4000), @tt varchar (4000);
declare gC cursor
for
select sAMAccountName, distinguishedName
from openquery
(ADSI,'SELECT sAMAccountName, distinguishedName
FROM ''LDAP:// YourADServerHere''
WHERE objectCategory = ''group''')
open gC
FETCH NEXT FROM gC INTO @t, @t2
WHILE @@FETCH_STATUS = 0
BEGIN
set @ot = '''SELECT sAMAccountName, displayName
FROM ''''LDAP://YourADServerHere''''
WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''
AND memberOf=''''' + @t2 + '''''';
set @tt = 'select '+ ''''+@t+'''' +' As groupNm, sAMAccountName, displayName from openquery(ADSI,'+ @ot +''') order by sAMAccountName'
insert into #MemberOfGroups(groupNm, sAMAccountName, displayName)
EXEC (@tt)
FETCH NEXT FROM gC INTO @t, @t2
END
CLOSE gC
DEALLOCATE gC
drop table #MemberOfGroups
Since I already have that query to find all users/persons in all groups, Inow just need one to show me groups within groups.
Thank you in advance ye SQL gurus.
December 6, 2013 at 3:28 pm
Keep in mind I don't know the answer, I am postulating ideas..
First glance is you need to enumerate the groups and see if any of their members are other groups. Also keep in mind this query will "break" for large organizations, the LDAP query will only return about 900 records (I think that is the number).
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply