Our admin folk just applied SQL 2000 SP4 on a W2K3 machine, and I noticed system function suser_sname() is not working normally.
I have an sp that extracts suser_sname(sid) from master..sysprocesses for statistics purpose. Since the SP4 upgrade, suser_sname() will return NULL on some of the Windows domain sid, but not the others.
I also noticed sid column in sysprocesses is a varbinary(86), where sid column in sysxlogins is a varbinary(85). I am not sure if sysprocesses was modified by SP4 or has it always been this way ?
Any help is appreciated.
I did get a confirmation from Microsoft that this is a bug generated by SP4.
This problem is contributed by the data type inconsistency in the system tables. SID column in sysprocesses is a binary(86), while defined as a varbinary(85) in syslogins.
The work around is to cast the SID to a valid fix length of binary(28) in order for suser_sname() to work.
Microsoft has no known fix plan for the time being. They note Yukon isn't affected by this bug.
An additional question for you THANG,
Did this bug also affect using Suser_sname() without a sid to get the name of the server login?
I am considering applying SP4, but I use this function for housekeeping.
I guess the answer to your question is it depends.
With my limited testing in our environment, I've found this issue only affects NT login via group membership.
1) If Joe has access to SQL Server via his personal NT account, then in 'syslogins', you'll see a record for Joe with a sid value. When Joe logins to SQL Server, this sid value is used to populate 'sysprocesses' record, so no problem for suser_sname() here.
2) If Joe only has access to SQL Server via an NT group (Joe is a member of 'NTGroupA', and this group was granted access to SQL Server), When Joe logins to SQL Server, the server has to derive Joe's sid value via the NT group. Somewhere along this line, the sid value wasn't properly formatted, so if you run suser_sname() against this sid in sysprocesses, it would not match, therefore null result.
I hope this help.