SQL Azure: list all logins and users

问题:

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?

回答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.

The unfortunate part is that you cannot discover the login name for the SID while connected to the user database. You will need to connect separately the master database once you have the sid and query sys.sys_logins to get the name.

回答2:

When connected to the master DB you can run this query to make a list of the Logins and the Users

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

I hope this may work