SQL Query to get All AD Groups and its users in Active Directory

_{Wednesday, July 23, 2014 10:06 AM}

Hi,

   Is there any query to get all AD groups and its user in an instance of a SQL server?

_{Wednesday, July 23, 2014 10:58 AM ✅Answered}

Check this blog. http://www.mikefal.net/2011/04/18/monday-scripts-%E2%80%93-xp_logininfo/

It will give you more than what is required. If you dont want the extra information,then you can try this.. I took the query and removed the bits that you might not require.

declare @winlogins table
(acct_name sysname,
acct_type varchar(10),
act_priv varchar(10),
login_name sysname,
perm_path sysname)

declare @group sysname

declare recscan cursor for
select name from sys.server_principals
where type = 'G' and name not like 'NT%'

open recscan
fetch next from recscan into @group

while @@FETCH_STATUS = 0
begin
insert into @winlogins
exec xp_logininfo @group,'members'
fetch next from recscan into @group
end
close recscan
deallocate recscan

select
u.name,
u.type_desc,
wl.login_name,
wl.acct_type
from sys.server_principals u
inner join @winlogins wl on u.name = wl.perm_path
where u.type = 'G'
order by u.name,wl.login_name

Regards, Ashwin Menon My Blog - http:\sqllearnings.com

_{Wednesday, July 23, 2014 10:16 AM}

Hi,

The below query will give all the groups added as logins in an instance of SQL Server.

select * from sys.server_principals where type = 'G'

If you want to know all the members of any of these groups run the below command .

xp_logininfo 'Group_name' , 'members'

You can easily create a cursor which loops through each row in the first query and uses the groupname as input for second query, if you want it together as a list.

Regards, Ashwin Menon My Blog - http:\sqllearnings.com

_{Wednesday, July 23, 2014 10:36 AM}

Hi,

  Thanks. I need all groups and the users available in that groups. What you given the second one was for single group.

_{Wednesday, July 23, 2014 10:53 AM}

Hello,

It's difficult from T-SQL side; if PowerShell would be ok, then you can use this script: List Member of Sql Server AD Group Logins

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Wednesday, July 23, 2014 11:07 AM}

It works for me. Thanks Ashwin.

_{Wednesday, July 23, 2014 5:23 PM}

Can we get all domain AD groups and its users available in all SQL server instance of a single domain?