获取用户及其有权访问数据库列表的SQL查询问题

你当前的查询会返回所有数据库，原因是直接将当前数据库的用户列表与所有数据库做无意义的关联，完全没考虑用户实际的访问权限。以下是正确的解决方案：

方法1：获取每个登录名对应的所有可访问数据库（聚合格式） #

使用动态SQL遍历所有数据库，收集登录名与可访问数据库的映射，再聚合为username@databasename格式：

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @Results TABLE (login_name NVARCHAR(128), db_name NVARCHAR(128));

-- 生成遍历所有数据库的查询语句
SELECT @SQL += '
USE [' + name + '];
INSERT INTO @Results (login_name, db_name)
SELECT 
    sp.name,
    DB_NAME()
FROM 
    sys.database_principals dp
JOIN 
    sys.server_principals sp ON dp.sid = sp.sid
WHERE 
    dp.type IN (''S'', ''U'', ''G'') -- 筛选SQL用户、Windows用户/组
    -- 检查用户是否有数据库访问权限（含角色继承的权限）
    AND HAS_PERMS_BY_NAME(DB_NAME(), ''DATABASE'', ''CONNECT'') = 1
'
FROM sys.databases 
WHERE database_id > 0 -- 排除系统内部数据库
AND name NOT IN ('tempdb'); -- 可选：排除临时数据库

-- 执行动态SQL并收集结果
EXEC sp_executesql @SQL, N'@Results TABLE (login_name NVARCHAR(128), db_name NVARCHAR(128))', @Results;

-- 聚合结果为要求的格式
SELECT 
    login_name,
    STRING_AGG(CONCAT(login_name, '@', db_name), ', ') AS db_access_list
FROM @Results
GROUP BY login_name;

方法2：获取逐条的权限记录 #

如果不需要聚合，直接返回每条username@databasename记录，可以用更简洁的动态SQL：

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL += '
USE [' + name + '];
SELECT 
    CONCAT(sp.name, ''@'', DB_NAME()) AS db_access
FROM 
    sys.database_principals dp
JOIN 
    sys.server_principals sp ON dp.sid = sp.sid
WHERE 
    dp.type IN (''S'', ''U'', ''G'')
    AND HAS_PERMS_BY_NAME(DB_NAME(), ''DATABASE'', ''CONNECT'') = 1
'
FROM sys.databases 
WHERE database_id > 0
AND name NOT IN ('tempdb');

EXEC sp_executesql @SQL;

原查询无效的原因 #

• sys.database_principals仅包含当前执行查询的数据库中的用户，不是所有数据库的用户
• 直接与sys.databases做join，相当于把当前数据库的每个用户和所有数据库强行关联，完全没有验证用户是否真的能访问这些数据库，所以会返回所有数据库的结果。

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