Zain Rizvi Zain Rizvi - 1 year ago 77
SQL Question

SQL Query to get mapping of all users to their logins

What's a query that I can use to get a list of all logins associated with each user in SQL Azure?

So far I've found the following two queries to get all users and all logins, but I haven't found any way to see which user goes with which login:

SELECT * from sys.sql_logins -- get all logins
SELECT * from sys.sysusers -- get all users

In case you find it helpful, here's the documentation for the structures of those the tables:


Column names: name, principal_id, sid, type, type_desc, is_disabled, create_date, modify_date, default_database_name, default_language_name, credential_id, is_policy_checked, is_expiration_checked, password_hash


Column names: uid, status, name, sid, roles, createdate, updatedate, altuid, password, gid, environ, hasdbaccess, islogin, isntname, isntgroup, isntuser, issqluser, isaliased, issqlrole, isapprole

Answer Source

It's hard to tell you your correct answer b/c we don't know the structure of your tables. If you share that we can help more. But below should get you to where you need to go.

They way to do it is by a MySQL JOIN. In this case you should use a INNER or OUTER JOIN depending on how your database is structured.

If you have 2 tables that are structured below you can do an FULL OUTER JOIN


| sid| userID |  name   |
| 1  | 1      |  ssmith |
| 2  | 2      |  bbob   | 


| sid| name      | 
| 1  | Sam Smith |  
| 2  | Billy Bob |   

You can use the following query to do it

SELECT as userName, as login 
FROM sys.sysusers A 
     FULL OUTER JOIN sys.sql_logins B 
     ON A.sid = B.sid

This will result in :

| userName   | logins |
| Same Smith | ssmith |
| Billy Bob  | bbob   |

Here is a link to more types of MySQL Joins

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download