SQL Azure: list all logins and users

Question

I'm using V12 Azure SQL.

To list all logins (server level) we can use this query on master database:

SELECT * FROM sys.sql_logins;

To list all users (database level) we can use this query on a specific database:

SELECT * FROM sys.sysusers;

But how to get the correspondence between logins and users?

Where is the system table that stores this correspondence?

Answer 1

To find the login mapped for a user, look at the sid column from sys.sysusers.

This value corresponds to the sid column from sys.sql_logins in the master database.

Unfortunately, you cannot discover the login name for the SID while connected to the user database. You must connect separately to the master database once you have the sid and query sys.sql_logins to get the name.