获取所有账号在每个db的数据库角色清单

话不多说，直接上脚本如下：

USE master
GO 

IF OBJECT_ID('tempdb..#tmp') is not null
DROP TABLE #tmp

CREATE TABLE #tmp(id int primary key identity(1,1), username nvarchar(200), databasename nvarchar(200), privs nvarchar(200))
DECLARE @databasename nvarchar(1000)
DECLARE @sql nvarchar(max)

--.过滤database_name
DECLARE database_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE state = 0 --AND name LIKE 'xxx%' 
ORDER BY name 

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'USE ' + QUOTENAME(@databasename) + ';
  select u.name, db_name(), r.name
  from sys.database_principals u
  inner join sys.database_role_members map on u.principal_id = map.member_principal_id
  inner join sys.database_principals r on map.role_principal_id = r.principal_id
  inner join sys.sql_logins l on u.name = l.name and is_disabled = 0
  where u.name <> ''dbo'''

  INSERT INTO #tmp (username, databasename, privs)
  EXEC (@sql)

  FETCH NEXT FROM database_cursor INTO @databasename
END
CLOSE database_cursor
DEALLOCATE database_cursor

INSERT INTO #tmp (username, databasename, privs)
SELECT u.name, NULL, p.name 
FROM sys.server_role_members r
INNER JOIN sys.server_principals p ON r.role_principal_id = p.principal_id AND p.type = 'R' 
INNER JOIN sys.sql_logins u ON r.member_principal_id = u.principal_id AND u.is_disabled = 0 
WHERE r.member_principal_id <> 1 

SELECT t2.username, t2.databasename, group_concat = STUFF(( SELECT ',' + privs FROM #tmp AS t1 WHERE t1.username = t2.username AND t1.databasename = t2.databasename FOR xml path('')) , 1 , 1 , '') 
FROM #tmp AS t2 
WHERE t2.username IN(SELECT name FROM sys.sql_logins) 
AND t2.databasename IS NOT NULL 
GROUP BY t2.username, t2.databasename

脚本执行结果如下表示：