How to get current login ID (SQL Server 2000)

I'm totally stumped on this one. It's not as easy as it first sounds.

Code:

---

SELECT SYSTEM_USER;

---

The above returns login name but the problem is when an SQL login uses windows authentication and is mapped to a user group. Thus, my login name is "MyDomain\Domain Admins" but the above function returns: "MyDomain\simon_m".

How can I possibly determine what the actual SQL login used was for the current connection? :confused:

Background: I need to do this because I want to assess whether the current connection is able to VIEW SERVER STATE (which is a server permission). If I can pin down which login I am, I can work it out. But if anyone has any alternatives, I am open to suggestions?

Would this work for you?

Code:

---

select CURRENT_USER;

---

No, that simply returns 'dbo'.

To help further elucidate my problem, take a look at this SQL:

Code:

---

SELECT sl.name AS SystemLoginName, sp.loginame AS ProcessLoginName
FROM master..syslogins sl
RIGHT JOIN master..sysprocesses sp ON sl.sid = sp.sid
WHERE sp.spid = @@SPID

---

I'm joining syslogins to sysprocesses. When I've logged in with an SQL authenticated login or a windows authenticated login that corresponds to a single windows user, I get the same value in both columns. When I've logged in with a windows authenticated login that corresponds to a windows user group, my SystemLoginName column is NULL.

sysprocesses.sid usually points to a row in syslogins but not in this specific case. Presumably because each user must have a unique ID. But then how can I trace back which login my process relates to?