Get database users and all other databases they have access to

databases-users-access-other-databases.sql

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp (
	[database_name]		sysname			NOT NULL
,	[user_name]			sysname			NOT NULL
,	[type_desc]			nvarchar(120)	NOT NULL
,	[user_sid]			varbinary(85)	NULL
,	[login_name]		sysname			NULL
);
GO

EXEC sp_MSforeachdb N'USE [?]; 
INSERT	#tmp
SELECT	DB_NAME() AS [database_name]
	,	U1.[name] AS [user_name]
	,	U1.[type_desc]
	,	U1.[sid] AS [user_sid]
	,	L1.[name] AS [login_name]
FROM	sys.database_principals AS U1
LEFT
JOIN	sys.server_principals AS L1
  ON	U1.[sid] = L1.[sid]
WHERE	U1.[type_desc] <> ''DATABASE_ROLE''
  AND	U1.[name] NOT IN (''dbo'', ''sys'', ''guest'', ''INFORMATION_SCHEMA'');
';
GO

DECLARE	@database_name sysname = N'DtabaseName';

WITH cte AS
(
	SELECT	U1.*, U2.[database_name] AS other_database_name
	FROM	#tmp AS U1
	LEFT
	JOIN	#tmp AS U2
	  ON	U1.[user_sid] = U2.[user_sid]
	 AND	U2.[database_name] <> U1.[database_name]
	WHERE	U1.[database_name] = @database_name
)
SELECT
	U1.[database_name], U1.[user_name], U1.[type_desc], U1.[login_name]
,	STUFF((
		SELECT	N'', ', [' + U2.other_database_name + ']'
		FROM	cte AS U2
		WHERE	U1.[user_sid] = U2.[user_sid]
		ORDER BY U2.other_database_name
		FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)')
	, 1, 2, N'') AS other_database_names
FROM	cte AS U1
GROUP BY
	U1.[database_name], U1.[user_name], U1.[type_desc], U1.[login_name], U1.[user_sid]
ORDER BY U1.[database_name], U1.[user_name]