Using SQL Server script to get list of users and their database roles in SQL Server

use Database_Name;

go 

SELECT  dPrinc.name AS [Members],dRole.name AS [Database Role Name]

FROM sys.database_role_members AS dRo  

JOIN sys.database_principals AS dPrinc  

    ON dRo.member_principal_id = dPrinc.principal_id  

JOIN sys.database_principals AS dRole  

    ON dRo.role_principal_id = dRole.principal_id;