For every database return non-db_datareaders, dbo, and ## users database permissions.
-- Declaration of table variable for the storage of results
DECLARE @result AS TABLE (
database_name SYSNAME,
role_name SYSNAME,
principal_name SYSNAME
);
-- Insert the resultset from each database
INSERT INTO @result
EXEC sp_msforeachdb N'USE [?]
SELECT CASE WHEN DB_NAME() IN (''master'',''msdb'',''model'',''tempdb'') THEN ''*'' + DB_NAME() ELSE DB_NAME() END AS database_name,
USER_NAME(role_principal_id) AS [Role],
USER_NAME(member_principal_id) AS [User]
FROM sys.database_principals p
INNER JOIN sys.database_role_members m
ON (p.principal_id = m.member_principal_id)
INNER JOIN sys.database_principals r
ON (m.role_principal_id = r.principal_id)
WHERE r.name NOT IN (''db_datareader'')
AND USER_NAME(member_principal_id) <> ''dbo''
AND USER_NAME(member_principal_id) NOT LIKE ''##%''
ORDER BY [Role],
[User]
;'
-- Display the result
SELECT *
FROM @result
ORDER BY database_name,
principal_name;