This will add a roles column with all roles for each user:

SELECT 
	UserID, 
	DisplayName, 
	Email,
	(
		SELECT 
			RoleName + ', '
		FROM
			{databaseOwner}{objectQualifier}Roles R
			INNER JOIN {databaseOwner}{objectQualifier}UserRoles UR ON (UR.RoleID=R.RoleID AND UR.UserID=U.UserID)
		WHERE 
			(UR.EffectiveDate IS NULL OR UR.EffectiveDate <= getdate()) AND (UR.ExpiryDate IS NULL OR getdate() < UR.ExpiryDate)
		FOR XML PATH('')
	) AS Roles 
FROM {databaseOwner}{objectQualifier}Users U
ORDER BY UserID ASC

All I can say that the query works on my installation. Using DotNetNuke 4.8.4 on SQL Server Express. I can see that the the query sql is not being displayed properly by the forum. Here it is again with another format:

SELECT
    UserID,
    DisplayName,
    Email,
    (
        SELECT
            RoleName + ', '
        FROM
            {databaseOwner}{objectQualifier}Roles R
            INNER JOIN {databaseOwner}{objectQualifier}UserRoles
                UR ON (UR.RoleID=R.RoleID AND UR.UserID=U.UserID)
        WHERE
            (UR.EffectiveDate IS NULL OR UR.EffectiveDate <= getdate())
            AND (UR.ExpiryDate IS NULL OR getdate() < UR.ExpiryDate)
        FOR
            XML PATH('')
    ) AS Roles
FROM
    {databaseOwner}{objectQualifier}Users U
ORDER BY
    UserID ASC