Which login belongs to which active directory group, handling through xp_logininfo.

 

Run the first piece of code with in the comment section first. This will generate print statement of  xp_logininfo. Copy that print statement and paste it in the second pieced of code in between ' ' otherwise it throws error.

/*
DECLARE @Stmt NVARCHAR(4000)
set @Stmt='SELECT ''EXEC master..xp_logininfo ''+''''''''''''+Name+''''''''''''+'',''+''''''''''members''''''''''
FROM sys.server_principals
where  [type]=''G'''
EXEC sp_executesql @Stmt
*/
declare @STMT2 VARCHAR(4000)
set @stmt2='
EXEC master..xp_logininfo ''domain1\group1'',''members''
EXEC master..xp_logininfo ''domain1\grop2'',''members''
EXEC master..xp_logininfo ''domain1\group3'',''members''
'
DECLARE @Table TABLE
(AccountName varchar(1000),[Type] varchar(10),prilage VARCHAR(50),MappedLoginName VARCHAR(1000),PermissionPath varchar(1000))
INSERT INTO @Table
EXEC (@STMT2)
SELECT @@Servername as Servername,* FROM @Table
WHERE AccountName like '%desh%'

The below query will give information about which windows login group is associated with which database and which database roles.

SELECT
    d.name AS 'DatabaseName',
    r.name AS 'RoleName',
    p.name AS 'WindowsGroupName'
FROM
    sys.database_role_members m
    JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
    JOIN sys.database_principals p ON m.member_principal_id = p.principal_id
    JOIN sys.databases d ON d.database_id = r.principal_id
WHERE
    r.type = 'R' -- Filter to retrieve only roles (not users)
	and d.name='databasename'
ORDER BY
    d.name, r.name;