SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP4 Breaks Suser_sname() ?


SP4 Breaks Suser_sname() ?

Author
Message
THANG HOANG
THANG HOANG
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 551

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.


Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12537 Visits: 14
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!
THANG HOANG
THANG HOANG
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 551

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.


Brian Munier
Brian Munier
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 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


THANG HOANG
THANG HOANG
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 551

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.


Brian Munier
Brian Munier
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 80
Thanks Thai, that is good to know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search