SQL query to get the list of SQL accounts and AD accounts having access on database/synapse and their accessibility

Question

Hi All,

I am using the below query to get the account details and their accessibility:
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

But this does not provide the accounts having granular access like access on particular table or schema, etc.

Can someone please help me with this query improvement?

Answer 1

The below query can work :

select princ.name,princ.type_desc,perm.permission_name,perm.state_desc,perm.class_desc,object_name(perm.major_id)
from
sys.database_principals princ
left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id--List database roles & members