Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP4 Breaks Suser_sname() ? Expand / Collapse
Author
Message
Posted Thursday, November 10, 2005 4:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:34 AM
Points: 25, Visits: 511

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.

Post #236452
Posted Monday, November 14, 2005 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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!
Post #236863
Posted Wednesday, November 16, 2005 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:34 AM
Points: 25, Visits: 511

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.

Post #237624
Posted Thursday, December 29, 2005 9:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, Visits: 80

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

Post #247262
Posted Wednesday, January 4, 2006 11:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:34 AM
Points: 25, Visits: 511

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.

Post #248206
Posted Friday, January 6, 2006 11:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, Visits: 80
Thanks Thai, that is good to know.
Post #249026
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse