Retrieve Logins and their associated Database Roles

To obtain the list of accounts at the database level along with their corresponding roles, execute the SQL script provided below:

USE tempdb

GO

SELECT GETDATE() AS [now];

SELECT @@SERVERNAME AS servername, [name] AS LoginName, [type_desc], create_date, sid,

CASE WHEN EXISTS (

SELECT 1 FROM sys.server_role_members AS M

JOIN sys.server_principals AS R ON M.role_principal_id = R.principal_id

WHERE LOWER(R.name) = 'sysadmin' AND M.member_principal_id = P.principal_id) THEN 1 ELSE 0 END AS is_sysadmin

INTO #tmp_Logins

FROM sys.server_principals AS P WHERE [type] IN ('S', 'U', 'G') AND is_disabled = 0

AND UPPER([name]) NOT LIKE 'NT SERVICE\%' AND UPPER([name]) NOT LIKE 'NT AUTHORITY\%';

SELECT servername, LoginName, [type_desc], create_date, is_sysadmin FROM #tmp_Logins;

EXEC sp_MSforeachdb 'USE [?];

IF DB_NAME() NOT IN (''master'', ''model'', ''tempdb'', ''msdb'')

BEGIN

SELECT DB_NAME() AS [database], SL.LoginName AS LoginName, R.[name] AS RoleName

FROM sys.database_principals AS DP JOIN #tmp_Logins AS SL ON DP.sid = SL.sid

LEFT JOIN sys.database_role_members AS M ON M.member_principal_id = DP.principal_id

LEFT JOIN sys.database_principals AS R ON R.principal_id = M.role_principal_id AND R.is_fixed_role = 1

END

';

DROP TABLE #tmp_Logins;

Here is an example of the result: