Zain Rizvi Zain Rizvi - 1 month ago 5
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:

sys.sql_logins:
https://msdn.microsoft.com/en-us/library/ms174355.aspx?f=255&MSPPError=-2147217396

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

sys.sysusers: https://msdn.microsoft.com/en-us/library/ms179871.aspx

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

Answer

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

[sys.sql_logins]

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

[sys.sysusers]

| 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

https://www.sitepoint.com/understanding-sql-joins-mysql-database/

http://dev.mysql.com/doc/refman/5.7/en/join.html

http://www.w3schools.com/sql/sql_join.asp

Comments