Query below returns list of users excluding system ones in Teradata database with their roles.

Query

SELECT  UserName,
        CreatorName,
        DefaultDatabase,
        CreateTimeStamp,
        TRIM(TRAILING ',' FROM
            XMLAGG(R.RoleName || ',')(VARCHAR(255))) AS Roles
FROM DBC.UsersV U
LEFT JOIN DBC.RoleMembersV R
ON  U.Username = R.Grantee
WHERE   U.Username NOT IN ('TDPUSER', 'Crashdumps', 'tdwm', 'DBC',
        'LockLogShredder', 'TDMaps', 'Sys_Calendar', 'SysAdmin', 
        'SystemFe', 'External_AP')
GROUP BY    UserName,
            CreatorName,
            DefaultDatabase,
            CreateTimeStamp
ORDER BY U.Username;

Columns

• UserName - name of the user
• CreatorName - name of the user who created user
• DefaultDatabase - user's default database
• CreateTimeStamp - time when user was created
• Roles - granted roles to user

Rows

• One row represents one user in the database
• Scope of rows: non-system users in the database
• Ordered by username

Sample results