Question about SQL Server roles

From: Carl Imthurn (nospam_at_all.thanks)
Date: 12/09/03 

Date: Tue, 09 Dec 2003 06:46:06 -0800

Can someone explain to me why I get two different results here?
What I'm trying to do is return a recordset with *all* roles a user belongs to.
Unless I pass in the actual username, I only get one role. I'm using myself as the example here.

-- use CURRENT_USER

SELECT R.name AS RoleName
   FROM (sysusers U INNER JOIN sysmembers M ON U.uid = M.memberuid)
      INNER JOIN sysusers R ON R.uid = M.groupuid
   WHERE R.issqlrole = 1 AND U.name = CURRENT_USER

RoleName
--------------------
db_owner

(1 row(s) affected)

-- Change CURRENT_USER to USER_NAME()

SELECT R.name AS RoleName
   FROM (sysusers U INNER JOIN sysmembers M ON U.uid = M.memberuid)
      INNER JOIN sysusers R ON R.uid = M.groupuid
   WHERE R.issqlrole = 1 AND U.name = USER_NAME()

RoleName
--------------------
db_owner

(1 row(s) affected)

-- change CURRENT_USER to the actual user name

SELECT R.name AS RoleName
   FROM (sysusers U INNER JOIN sysmembers M ON U.uid = M.memberuid)
      INNER JOIN sysusers R ON R.uid = M.groupuid
   WHERE R.issqlrole = 1 AND U.name = ‘cimthurn’ -- actual user name

RoleName
--------------------
db_owner
QRSysAdmins
HygieneEvaluators

(3 row(s) affected)

Why do I get all my roles only when I use my actual user name?

Any insight will be appreciated -- thanks in advance.

Carl