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

Question

Hi Team,

I want to fect below information from synapse using commands ..

AD Group -> Database Role -> Schemas -> Access Given

1) AD Group - This are the AD Groups mapped into my Synapse

2) Databse Role - each of the AD Groups has access on particular DB role

3) Schemas - each of the database role has access over schema/tables

4) Access Given - checking whether that schema read-write access or read-only access ..

how can i get results like below using commands in synapse please check below image ..

Thanks & Regards,
Mahi

Answer 1

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;

Answer 2

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