SQL Azure:列出所有登录名和用户 [英] SQL Azure: list all logins and users

问题描述

我正在使用V12 Azure SQL.

要列出所有登录名(server level)，我们可以在master数据库上使用此查询:

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

SELECT * FROM sys.sql_logins;

要列出所有用户(database level)，我们可以在特定数据库上使用此查询:

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

SELECT * FROM sys.sysusers;

但是如何获取logins和users之间的对应关系?

But how to get the correspondence between logins and users?

system table存储此对应关系的位置在哪里?

Where is the system table that stores this correspondence?

推荐答案

要查找为用户映射的登录名，请查看sys.sysusers中的sid列.

To find the login mapped for a user, look at the sid column from sys.sysusers.

此值对应于主数据库中sys.sql_logins中的sid列.

This value corresponds to the sid column from sys.sql_logins in the master database.

不幸的是，当连接到user database时，您无法找到SID的登录名.拥有sid并查询sys.sys_logins以获得名称后，您将需要分别连接master数据库.

The unfortunate part is that you cannot discover the login name for the SID while connected to the user database. You will need to connect separately the master database once you have the sid and query sys.sys_logins to get the name.

这篇关于SQL Azure:列出所有登录名和用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！