Find out to which AD group a login belongs or find out who the members of a group are.

use master
go
EXEC xp_logininfo N'<your AD login>','ALL';
GO

use master
go
EXEC xp_logininfo N'<your AD Group>','MEMBERS';
GO

So what can we make with these functions?
Well I was for example intrested in who are in the active directory groups and so who in that group have what kind of permissions on the databases.

This is what I made

use master
GO

DECLARE @LoginName nvarchar(256)

SET NOCOUNT ON; 

-- Create a temp table to store exceptions
drop table if exists #Exceptions;
create table #Exceptions
(
   Id int identity(1,1)
  ,ErrorNumber int
  ,ErrorSeverity int
  ,ErrorState int
  ,ErrorMessage nvarchar(1024)
)

-- Create a temp table to store all AD groups that are mapped in SQL server
drop table if exists #WindowsGroups;
create table #WindowsGroups
(
   GroupName nvarchar(256)
  ,CheckedForMembers bit default 0
)
insert into #WindowsGroups (GroupName)
select name as 'GroupName' FROM sys.server_principals where type_desc = 'WINDOWS_GROUP'

-- create a temp stage table
DROP TABLE IF EXISTS #GroupMembers;
CREATE TABLE #GroupMembers
(
   Id int identity(1,1)
  ,AccountName nvarchar(256)
  ,AccountType nvarchar(128)
  ,Privilege nvarchar(256)
  ,MappedLoginName nvarchar(256)
  ,PermissionsPath nvarchar(512)
)
-- create a temp stage table
DECLARE @GroupMembers TABLE 
(
   AccountName nvarchar(256)
  ,AccountType nvarchar(128)
  ,Privilege nvarchar(256)
  ,MappedLoginName nvarchar(256)
  ,PermissionsPath nvarchar(512)
)

DECLARE GroupCursor CURSOR FOR
SELECT 
 GroupName
,SqlCommand = 
  case 
    when type_desc = 'WINDOWS_GROUP' 
    then concat('EXEC xp_logininfo N''',name, ''', ''members''',';') 
  end
FROM sys.server_principals as s
join #WindowsGroups as g on s.name = g.GroupName and CheckedForMembers = 0
where type_desc = 'WINDOWS_GROUP'

DECLARE @GroupName nvarchar(256), @xp_logininfo nvarchar(max)
OPEN GroupCursor  
FETCH NEXT FROM GroupCursor INTO @GroupName, @xp_logininfo
WHILE (select top 1 1 FROM sys.server_principals as s join #WindowsGroups as g on s.name = g.GroupName and CheckedForMembers = 0) = 1
BEGIN  
  begin try
    print @GroupName
    update #WindowsGroups 
    set CheckedForMembers = 1 
    where GroupName = @GroupName

    insert into @GroupMembers (AccountName, AccountType, Privilege, MappedLoginName, PermissionsPath)
    exec sp_executesql @xp_logininfo

    -- insert all users into temp #GroupMembers table
    insert into #GroupMembers (AccountName, AccountType, Privilege, MappedLoginName, PermissionsPath)
    select AccountName, AccountType, Privilege, MappedLoginName, PermissionsPath
    from @GroupMembers
    where AccountType = 'user'

    -- add newly found groups into the #Windows templ table so that it too will loop by this cursor
    insert into #WindowsGroups (GroupName)
    select AccountName from @GroupMembers where AccountType = 'group' and AccountName not in (select GroupName from #WindowsGroups)

  end try
  begin catch
    -- collect exceptions into temp #exception table
    insert into #Exceptions (ErrorNumber, ErrorSeverity, ErrorState, ErrorMessage)
    SELECT  
       ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_MESSAGE() AS ErrorMessage;  
  end catch	
  FETCH NEXT FROM GroupCursor INTO @GroupName, @xp_logininfo
END 
CLOSE GroupCursor  
DEALLOCATE GroupCursor 

-- Find the AD groups to whitch a AD Login belongs
DROP TABLE IF EXISTS #AllSqlLoginsExtraced;
CREATE TABLE #AllSqlLoginsExtraced
(
   Id int identity(1,1)
  ,AccountName nvarchar(256)
  ,AccountType nvarchar(128)
  ,Privilege nvarchar(256)
  ,MappedLoginName nvarchar(256)
  ,MemberOfAdGroupInSql nvarchar(512)
);

DECLARE @xp_logininfo2 nvarchar(256), @AccountName nvarchar(256)
DECLARE LoginCursor CURSOR FOR
with CteLogin
as
(
  select distinct AccountName from #GroupMembers
  union
  select Name FROM sys.server_principals where type_desc = 'WINDOWS_LOGIN'
) 
select concat('exec xp_logininfo ''', AccountName ,''', ''all'''), AccountName
from CteLogin

OPEN LoginCursor  
FETCH NEXT FROM LoginCursor INTO @xp_logininfo2, @AccountName
WHILE @@FETCH_STATUS = 0
BEGIN  
  begin try
    insert into #AllSqlLoginsExtraced (AccountName, AccountType, Privilege, MappedLoginName, MemberOfAdGroupInSql)
    exec sp_executesql @xp_logininfo2

    if @@ROWCOUNT = 0
    begin 
      insert into #AllSqlLoginsExtraced (AccountName, MemberOfAdGroupInSql) values (@AccountName, '{NON}')
    end
  end try
  begin catch
    insert into #Exceptions (ErrorNumber, ErrorSeverity, ErrorState, ErrorMessage)
    SELECT  
       ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_MESSAGE() AS ErrorMessage;  
  end catch	
  FETCH NEXT FROM LoginCursor INTO @xp_logininfo2, @AccountName
END 
CLOSE LoginCursor  
DEALLOCATE LoginCursor 

/*
url: https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SET NOCOUNT ON;

declare @SqlCommand nvarchar(max)
set @SqlCommand = '
--List all access provisioned to a sql user or windows user/group directly
SELECT 
 DB_NAME(DB_ID()) AS DatabaseName
,[UserName] = CASE princ.[type]
WHEN ''S''
THEN princ.[name]
WHEN ''U''
THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END
,UserType = princ.[type_desc]
--,[UserType] = CASE princ.[type]
--WHEN ''S''
--THEN ''SQL User''
--WHEN ''U''
--THEN ''Windows User''
--END
,[DatabaseUserName] = princ.[name]
,[Role] = NULL
,[PermissionType] = PERM.[permission_name]
,[PermissionState] = PERM.[state_desc]
,[ObjectType] = obj.type_desc
,[ObjectName] = OBJECT_NAME(PERM.major_id)
,[ColumnName] = col.[name]
FROM sys.database_principals princ
LEFT JOIN sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions PERM ON PERM.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.columns col ON col.[object_id] = PERM.major_id
AND col.[column_id] = PERM.[minor_id]
LEFT JOIN sys.objects obj ON PERM.[major_id] = obj.[object_id]
WHERE princ.[type] IN (''S'',''U'')
UNION
SELECT 
 DB_NAME(DB_ID()) AS DatabaseName
,[UserName] = CASE memberprinc.[type]
WHEN ''S''
THEN memberprinc.[name]
WHEN ''U''
THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END
,UserType = memberprinc.[type_desc]
--,[UserType] = CASE memberprinc.[type]
--WHEN ''S''
--THEN ''SQL User''
--WHEN ''U''
--THEN ''Windows User''
--END
,[DatabaseUserName] = memberprinc.[name]
,[Role] = roleprinc.[name]
,[PermissionType] = PERM.[permission_name]
,[PermissionState] = PERM.[state_desc]
,[ObjectType] = obj.type_desc
,[ObjectName] = OBJECT_NAME(PERM.major_id)
,[ColumnName] = col.[name]
FROM sys.database_role_members members
INNER JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
INNER JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions PERM ON PERM.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col ON col.[object_id] = PERM.major_id
AND col.[column_id] = PERM.[minor_id]
LEFT JOIN sys.objects obj ON PERM.[major_id] = obj.[object_id]
UNION
SELECT 
 DB_NAME(DB_ID()) AS DatabaseName
,[UserName] = ''{All Users}''
,[UserType] = ''{All Users}''
,[DatabaseUserName] = ''{All Users}''
,[Role] = roleprinc.[name]
,[PermissionType] = PERM.[permission_name]
,[PermissionState] = PERM.[state_desc]
,[ObjectType] = obj.type_desc
,[ObjectName] = OBJECT_NAME(PERM.major_id)
,[ColumnName] = col.[name]
FROM sys.database_principals roleprinc
LEFT JOIN sys.database_permissions PERM ON PERM.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col ON col.[object_id] = PERM.major_id
AND col.[column_id] = PERM.[minor_id]
INNER JOIN --All objects
sys.objects obj ON obj.[object_id] = PERM.[major_id]
WHERE
roleprinc.[type] = ''R'' AND
roleprinc.[name] = ''public'' AND
obj.is_ms_shipped = 0
ORDER BY princ.[Name]
,OBJECT_NAME(PERM.major_id)
,col.[name]
,PERM.[permission_name]
,PERM.[state_desc]
,obj.type_desc --perm.[class_desc]'

drop table if exists #SqlSecurity
create table #SqlSecurity
(
   DatabaseName nvarchar(512)
  ,UserName nvarchar(256)
  ,UserType nvarchar(256)
  ,DatabaseUserName nvarchar(512)
  ,RoleName nvarchar(256)
  ,PermissionType nvarchar(256)
  ,PermissionsState nvarchar(256)
  ,ObjectType nvarchar(256)
  ,ObjectName nvarchar(256)
  ,ColumName nvarchar(256)
)

DECLARE @DbName VARCHAR(256) -- database name 
DEclare @SqlCommand2 nvarchar(max)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DbName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
  DECLARE @ExecuteSql nvarchar(max) = 'use ['+@DbName+']; ' + @SqlCommand
  INSERT INTO #SqlSecurity 
  EXEC sp_executesql @ExecuteSql
  FETCH NEXT FROM db_cursor INTO @DbName 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

-- create indexes
create nonclustered index ix_#AllSqlLoginsExtraced_AccountName on #AllSqlLoginsExtraced(AccountName) with (fillfactor = 100)
create nonclustered index ix_#AllSqlLoginsExtraced_MemberOfAdGroupInSql on #AllSqlLoginsExtraced(MemberOfAdGroupInSql) with (fillfactor = 100)
create nonclustered index ix_#SqlSecurity_DatabaseUserName on #SqlSecurity(DatabaseUserName) with (fillfactor = 100)

-- drop temp tables we no longer need
drop table #WindowsGroups
drop table #GroupMembers


-- To return the members of the server roles ( SQL Server only), execute the following statement.
SELECT DISTINCT
 sRole.name AS [ServerRoleName] 
,sPrinc.name AS [LoginOrGroup]
,a.AccountName as MembersOfGroup

FROM sys.server_role_members AS sRo  
JOIN sys.server_principals AS sPrinc  
    ON sRo.member_principal_id = sPrinc.principal_id  
JOIN sys.server_principals AS sRole  
    ON sRo.role_principal_id = sRole.principal_id
LEFT JOIN #AllSqlLoginsExtraced as a on sPrinc.name = a.MemberOfAdGroupInSql
where sPrinc.name = @LoginName or @LoginName is null;

select 
 AccountName
,Privilege
,MemberOfAdGroupInSql
,s.*
from #AllSqlLoginsExtraced as a
left join #SqlSecurity as s on a.AccountName = s.DatabaseUserName or a.MemberOfAdGroupInSql = s.DatabaseUserName
where @LoginName is null or @LoginName = AccountName or @LoginName = s.DatabaseUserName
order by MemberOfAdGroupInSql asc


-- show exceptions
select * from #Exceptions
GO