Returning role memberships FROM sql Server

A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.

Well if you have a table of logged in users you just have to do an Inner Join

SELECT *
FROM  LoggedInUsers liu INNER JOIN Roles r ON liu.role_id = r.id