List users and their database roles in Azure SQL

• Received a query to list all users active on the service with there roles and access to the different databases available on the service.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE
    u.type NOT IN('R', 'G')
ORDER BY
      UserName
    , RoleName;

---

📇 Additional Metadata

• 🗂 Type::note
• 🏷️ Tags::
• 📡 Status::🌲