Azure SQL logins roles permissions

 --for sql logins

SELECT * FROM sys.sql_logins;

SELECT * FROM sys.sysusers;

SELECT * FROM sys.database_principals 

--- to connect master sql logins to the users in the user databases --- run this in user database

select l.name as [login name],u.name as [user name] from sysusers u inner join sys.sql_logins l on u.sid=l.sid

-- For AAD logins:

SELECT * FROM sys.server_principals 

SELECT * FROM sys.database_principals 

-- To retrive role and it's granted members:

SELECT    roles.principal_id                            AS RolePrincipalID

  ,    roles.name                                    AS RolePrincipalName

  ,    database_role_members.member_principal_id    AS MemberPrincipalID

  ,    members.name                                AS MemberPrincipalName

FROM sys.database_role_members AS database_role_members  

JOIN sys.database_principals AS roles  

  ON database_role_members.role_principal_id = roles.principal_id  

JOIN sys.database_principals AS members  

  ON database_role_members.member_principal_id = members.principal_id

  order by 2;  

GO

-- display role and members

--select * FROM sys.database_role_members

--select * FROM sys.database_principals order by name

SELECT DP1.principal_id                            AS DBRolePrincipalID,

DP1.name AS DatabaseRoleName,   

DRM.member_principal_id    AS MemberDBPrincipalID,

    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 = 'R'

ORDER BY DP1.name;  

-- retrive database object level permissions granted

--select * from sys.database_permissions

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)

order by 3,2