Displaying mapping between sql_logins and database_principals

Question

How can this be done?

The documentation describes a function which appears to not exist. sp_helplogins is described here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/security-stored-procedures-transact-sql?view=azuresqldb-current

Running the below statement within master as server admin result in the error message:
SQL Error [2812] [S0062]: Could not find stored procedure 'sp_helplogins'.

The statement (without quotes): "EXEC sp_helplogins;"

I'd like a single-query approach to identify which users are assigned to each sql_login.

Thanks

Answer 1

As you can verify here sp_helplogin can be found on SQL Server instances biut not on Azure SQL instances.

To find the login (master database) mapped to a database user, look at the sid column from sys.sysusers on the user database (not in the master). 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.

Here you will find another way to do the mapping between logins and database users.