列出給定登錄名的所有映射使用者的查詢

查看特定登錄名的屬性時，可以看到映射到該登錄名的使用者列表：

我分析了 SQL Server Management Studio (SSMS)，發現 SSMS 一次連接到每個數據庫，並從 sys.database_permissions 檢索資訊

是否可以編寫單個查詢來檢索上面顯示的使用者映射資訊，或者我是否被迫使用游標或 sp_MSforeachdb 或類似的東西？

這是使用動態 SQL 的一種方法。如果不進行迭代，實際上沒有任何方法可以做到這一點，但這種方法比無證、不受支持和有缺陷的選項（例如sp_MSforeachdb（此處和此處的背景））安全得多。

這將獲得所有線上數據庫的列表、映射使用者（如果存在）、預設模式名稱以及它們所屬角色的逗號分隔列表。

DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
   p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS, 
   p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
   STUFF((SELECT N'','' + r.name 
     FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
     INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
     ON r.principal_id = rm.role_principal_id
     WHERE rm.member_principal_id = p.principal_id
     FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
   FROM sys.server_principals AS sp
   LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
   ON sp.sid = p.sid
   WHERE sp.name = @name '
 FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

在更現代的版本（2017+）上，我仍然會使用動態 SQL，但我會使用STRING_AGG()而不是FOR XML PATH，可能是這樣的：

DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max), 
       @sid  varbinary(85),
       @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS 
(
 SELECT dbid = CONVERT(varchar(11), database_id),
        qn = QUOTENAME(name)
   FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
       N'SELECT db = d.name, username = dp.name ' + @coll + ', 
       schemaname = dp.default_schema_name ' + @coll + ',
       roles = STRING_AGG(rp.name ' + @coll + ', N'','')
       FROM sys.databases AS d
       LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
       LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
       ON dp.principal_id = rm.member_principal_id
       LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
       ON rp.principal_id = rm.role_principal_id
       WHERE d.database_id = ' + dbid + N'
       GROUP BY d.name, dp.name, dp.default_schema_name'
   ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

在後一個範例中，如果您只希望將使用者映射到命名登錄的數據庫，只需將第一個左連接更改為內連接。

引用自：https://dba.stackexchange.com/questions/81595