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
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 A.name as userName, B.name 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