List of Database Users by Role

----- This gives a list of all users and their Group setup on the server
sp_helpuser

----- Table sysUsers contains list of all users in the database
SELECT * FROM DatabaseName..sysUsers

----- This query will return list of Users by Role
USE DatabaseName
GO

SELECT
SR.Name AS RoleName
, SU.Name AS UserName
FROM
sysUsers AS SU
INNER JOIN sysUsers AS SR ON SU.GID = SR.UID
WHERE
SU.GID <> SU.UID ----- Specifies that the record is not a group but a user
-- AND SU.Name <> 'dbo' ----- If you want to leave out System Users
-- AND SU.Name <> 'Guest' ----- If you want to leave out Guest Accounts
ORDER BY
RoleName
, UserName