Fetching info from synapse regarding ad groups,user in that user group using commands

Hi @Nandan Hegde using above queries i got results of ad group and user belong to it ..

but can we get like this

schemas/tables to which can be accessible by that AD group and type of access - Read, Read & write

Hey @Mahi Chippy
You can build on top of the below 2 commands:

SELECT r.name role_principal_name, m.name AS member_principal_name
FROM sys.database_role_members rm
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id

--List database roles & members
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type IN ( 'R')
ORDER BY DP1.name;