question

The desired output is a bit interesting. But here is a query that will work based on your requirements. That said, it doesn't always work to have this kind of output. For instance, there are times when a database user does not exist as a login. Based on your requirements, these cases will be missed. Here is a script to do what you requested. WITH presel AS (SELECT sp.sid --,dp.principal_id ,oa.DatabaseRole FROM sys.server_principals sp INNER JOIN sys.database_principals dp ON dp.sid = sp.sid OUTER APPLY (SELECT dpr.name AS DatabaseRole FROM sys.database_role_members SR INNER JOIN sys.database_principals dpr ON sr.role_principal_id = dpr.principal_id INNER JOIN sys.database_principals dpi ON SR.member_principal_id = dpi.principal_id WHERE dpi.sid = dp.sid) oa Union Select SP.sid,'Public' as DatabaseRole From sys.server_principals SP where type in ('u','s','g') And is_disabled = 0 ) SELECT SUSER_NAME(so.member_principal_id) as Account_name ,SUSER_NAME(so.role_principal_id) AS ServerRole ,DB_NAME() AS DatabaseName ,STUFF((SELECT ',' + DatabaseRole FROM presel SR WHERE SR.sid = sp.sid --OR SR.member_principal_id = so.role_principal_id FOR XML PATH('')),1,1,'') AS DatabaseRoles FROM sys.server_role_members so LEFT OUTER JOIN sys.server_principals sp ON so.member_principal_id = sp.principal_id I must admit to being baffled why somebody without access to create temp tables would be looking to run this on production. Based on that tidbit, I would bet you don't have permissions to look into the sys.server_principals catalog view. That will make the output significantly different as well.