Mi se pare că seamănă mai mult cu ceea ce face UNPIVOT, dar fiind vorba de SQL 2000, poţi folosi ceva de genul:

SELECT u.nume, r.rol, r.denumire
FROM utilizatori u INNER JOIN (
SELECT 'ADT' AS rol, id, denumire FROM roluri_ADT
UNION ALL SELECT 'ADT' AS rol, id, denumire FROM roluri_ADT
UNION ALL SELECT 'AGR' AS rol, id, denumire FROM roluri_AGR
UNION ALL SELECT 'CIV' AS rol, id, denumire FROM roluri_CIV
UNION ALL SELECT 'COM' AS rol, id, denumire FROM roluri_COM
UNION ALL SELECT 'INI' AS rol, id, denumire FROM roluri_INI
UNION ALL SELECT 'JUR' AS rol, id, denumire FROM roluri_JUR
UNION ALL SELECT 'LOC' AS rol, id, denumire FROM roluri_LOC
UNION ALL SELECT 'PET' AS rol, id, denumire FROM roluri_PET
UNION ALL SELECT 'REG' AS rol, id, denumire FROM roluri_REG
UNION ALL SELECT 'SAL' AS rol, id, denumire FROM roluri_SAL
UNION ALL SELECT 'SOC' AS rol, id, denumire FROM roluri_SOC
UNION ALL SELECT 'TAX' AS rol, id, denumire FROM roluri_TAX
UNION ALL SELECT 'URB' AS rol, id, denumire FROM roluri_URB
) r ON u.id=r.id WHERE u.id = @id_utilizator

Răzvan