Post reply

SP4 Breaks Suser_sname() ?

  • November 10, 2005 at 4:47 pm

    #102919

    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.

  • November 14, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • November 16, 2005 at 12:28 pm

    #604269

    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.

  • December 29, 2005 at 9:47 am

    #611955

    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

  • January 4, 2006 at 11:40 am

    #612698

    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.

  • January 6, 2006 at 11:55 am

    #613364

    Thanks Thai, that is good to know.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply