SQLServerCentral / SQL Server 7,2000 / Service Packs / SP4 Breaks Suser_sname() ? / Latest Posts

RE: SP4 Breaks Suser_sname() ?

Brian Munier — Fri, 06 Jan 2006 11:55:00 GMT

Thanks Thai, that is good to know.

RE: SP4 Breaks Suser_sname() ?

THANG HOANG — Wed, 04 Jan 2006 11:40:00 GMT

Hi Brian,

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.

For example:

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.

RE: SP4 Breaks Suser_sname() ?

Brian Munier — Thu, 29 Dec 2005 09:47:00 GMT

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.

Thanks,

Brian

RE: SP4 Breaks Suser_sname() ?

THANG HOANG — Wed, 16 Nov 2005 12:28:00 GMT

F.Y.I.

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.

SP4 Breaks Suser_sname() ?

Site Owners — Mon, 14 Nov 2005 08:00:00 GMT

No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!

SP4 Breaks Suser_sname() ?

THANG HOANG — Thu, 10 Nov 2005 16:47:00 GMT

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.

Thanks.