All geek questions in one place

SQL Azure: List All Users and Users

I am using V12 Azure SQL .

To list all logins ( server level ), we can use this query in the master database:

 SELECT * FROM sys.sql_logins;

To list all users ( database level ), we can use this query in a specific database :

 SELECT * FROM sys.sysusers;

But how to get correspondence between logins and users ?

Where does the system table store this correspondence?

+5
source share
2 answers

To find the username associated with the user, look at the sid column with sys.sysusers .

This value corresponds to the sid column of sys.sql_logins in the main database.

The unfortunate part is that you cannot find the login name for sid when connecting to the user database . You will need to separately mount the master database if you have sid and sys.sys_logins request to get the name.

+4
source

When connecting to the main database, you can run this query to make a list of logins and users

 select l.name as [login name],u.name as [user name] from sysusers u inner join sys.sql_logins l on u.sid=l.sid

I hope this might work

+1
source

Source: https://habr.com/ru/post/1247234/

More articles:


All Articles