Schema level information for azure synapse

Question

Hi,

We have 4 schemas in azure synapse sqlpool. we have different access levels for the users based on schemas.
If i want to run a report on schema level access for the users, i am always getting default_schema_name as "dbo" which is not correct. We have schemas like "ABC","DEF".

I can not find those schema information in the below query. Is there any other query i can use

SELECT *
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
where pe.permission_name in (
'DELETE'
,'EXECUTE'
,'INSERT'
,'CREATE TABLE'
,'UPDATE'
)

Answer 1

The below query :
SELECT DISTINCT pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role], pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
pe.permission_name, pe.class_desc, o.[name] AS 'Object'FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)

would give access details at object level. So you can get schema detail based on objects via sys.objects