How to get sql server login name and their roles in a query I Need to create a query in SQL Server that will return login name and role.

However there's connection between the tables
Here is a solution I found it will give you the list of memberships on every DB on the server.

DECLARE @DB_USers TABLE
(DBName sysname, 
 UserName sysname, 
 LoginType sysname, 
 AssociatedRole varchar(max),
 create_date datetime,
 modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'USE [?]
 SELECT ''?'' AS DB_Name,
 CASE prin.name 
    when ''dbo'' 
    THEN prin.name + '' (''+ (SELECT SUSER_SNAME(owner_sid) FROM master.sys.databases WHERE name =''?'') + '')'' 
    ELSE prin.name 
 END AS UserName,
   prin.type_desc AS LoginType,
   ISNULL(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,
   create_date,
   modify_date

 FROM sys.database_principals prin
   LEFT OUTER JOIN sys.database_role_members mem 
      ON prin.principal_id=mem.member_principal_id

 WHERE prin.sid IS NOT NULL 
   AND prin.sid NOT IN (0x00) 
   AND prin.is_fixed_role &lt;&gt; 1 
   AND prin.name NOT LIKE ''##%'''

SELECT dbname,
       username,
       logintype,
       create_date,
       modify_date,
       STUFF(

     (SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
      FROM @DB_USers user2

      WHERE user1.DBName=user2.DBName 
        AND user1.UserName=user2.UserName

 FOR XML PATH('')),1,1,'') AS Permissions_user
 FROM @DB_USers user1

 GROUP BY dbname,
          username,
          logintype,
          create_date,
          modify_date

--HAVING username IN (&lt;Use this to filter on multiple usernames.&gt;)

 ORDER BY DBName,username