This is a repost of https://moodle.org/mod/forum/discuss.php?d=362674#p1462556 by Thomas College- I had a similar problem and worked out how to do it, so thought others may find the information useful.
This query should return any role assignments (by roleID, but that's simple to change) for all enrolled users in a specified courseID.
SELECT
(SELECT CONCAT (u.firstname,' ',u.lastname) FROM mdl_user u WHERE u.id = ue.userid) AS user_name,
cr.rid AS role_id
FROM mdl_enrol e
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
LEFT JOIN (SELECT ctx.instanceid AS iid, ra.roleid AS rid, ra.userid AS uid
FROM mdl_context ctx
JOIN mdl_role_assignments ra ON ra.contextid = ctx.id
WHERE ctx.contextlevel = 50)
AS cr ON cr.iid = e.courseid AND cr.uid = ue.userid
WHERE e.courseid = **insert course ID number here**
Modifications I can think of would be to show this for all courses (group by courseid), to show roles by name or shortname depending if they're user-specified roles or not, and to use this as a subquery, for example if you need to compare it to which group the user is in (this is what I've used it for).
Happy to help with anything I can surrounding this.