SQL Server Login Details.
Server Role
SET NOCOUNT ON ;With Cte as ( SELECT @@servername as ServerName, p.name AS LoginName, CASE WHEN p.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled , p.type_desc as LoginType, r.type_desc as RoleType, r.name as RoleName FROM sys.server_principals r INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id WHERE r.type = 'R' UNION ALL select @@servername as ServerName, R.name AS LoginName, CASE WHEN r.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled , r.type_desc as LoginType, 'SERVER_ROLE' as RoleType, 'public' as RoleName FROM sys.server_principals r WHERE r.type IN ('S','U') and r.name not like '##MS%' ) --select * from cte SELECT DISTINCT r.ServerName, r.LoginName, r.IsEnabled, r.LoginType, r.RoleType, STUFF(( SELECT distinct ', '+ a.RoleName FROM cte a WHERE r.ServerName = a.ServerName AND r.LoginName = a.LoginName AND r.IsEnabled = a.IsEnabled AND r.LoginType = a.LoginType AND r.RoleType = a.RoleType For XML PATH ('')),1,1,'' ) as ServerRole FROM cte r order by ServerName, LoginType, RoleType, LoginName