How to get current login ID (SQL Server 2000)

[simonm]

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

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?

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?

[RhinoBull]

Would this work for you?

select CURRENT_USER;

[simonm]

No, that simply returns 'dbo'.

[simonm]

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

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?