Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 653
alexey Avatar asked Apr 15 '16 17:04

alexey


1 Answers

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.

like image 66
Lonny Bastien Avatar answered Sep 25 '22 00:09

Lonny Bastien