如何获取Azure SQL Server中的登录名、用户映射及数据库角色列表

以下SQL查询可遍历Azure SQL Server上的所有用户数据库，获取SQL身份验证登录名对应的数据库用户，以及该用户所属的数据库角色：

CREATE TABLE #LoginUserRoles (
    LoginName NVARCHAR(128),
    DatabaseName NVARCHAR(128),
    DBUserName NVARCHAR(128),
    DBRoles NVARCHAR(MAX)
)

DECLARE @DBName NVARCHAR(128)
DECLARE DB_Cursor CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0 -- 排除系统数据库且仅包含在线数据库

OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'
    INSERT INTO #LoginUserRoles (LoginName, DatabaseName, DBUserName, DBRoles)
    SELECT 
        sp.name AS LoginName,
        ''' + @DBName + ''' AS DatabaseName,
        dp.name AS DBUserName,
        STRING_AGG(r.name, '', '') AS DBRoles
    FROM ' + QUOTENAME(@DBName) + '.sys.database_principals dp
    JOIN sys.server_principals sp ON dp.sid = sp.sid
    LEFT JOIN ' + QUOTENAME(@DBName) + '.sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
    LEFT JOIN ' + QUOTENAME(@DBName) + '.sys.database_principals r ON drm.role_principal_id = r.principal_id
    WHERE sp.type = ''S'' -- 仅筛选SQL身份验证登录名
    AND dp.type IN (''S'', ''U'')
    GROUP BY sp.name, dp.name
    '
    EXEC sp_executesql @SQL

    FETCH NEXT FROM DB_Cursor INTO @DBName
END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

SELECT 
    LoginName AS [Login],
    DatabaseName AS [Database],
    DBUserName AS [DB User],
    DBRoles AS [DB Roles]
FROM #LoginUserRoles
ORDER BY DatabaseName, LoginName

DROP TABLE #LoginUserRoles

预期输出示例： #

内容的提问来源于stack exchange，提问作者Pradeep