how to know what are the current schemas are there in synapse

Question

how to know what are the current schemas are there in synapse and who are the users acceessing it with their access like read and write

Answer 1

Hey @Rohit Boddu ,
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;