Strange results in master..syslogins

Question

Strange results in master..syslogins

Mat Elkington

Valued Member

Points: 55
More actions
April 6, 2004 at 5:23 am

#101949

Hi,
I've noticed something slightly strange in our syslogins table.
When there is a password given, the entry for the password appears in the language column, the language appears in the denylogin column and so on, with each colum having the value of the column before it, until the value of the last column - loginname - is excluded from the table because it has the value of the bulkadmin column in it.
When there is NO password associated with the login, all the values for all the columns appear in their correct columns.
I've run this a couple of times and it is not a one off.
We use SQL Server 2000 SP3 on a Windows Server 2000 platform.
I'm just using a straight query:
SELECT * FROM master..syslogins.
I am slightly puzzled to say the least. Has anyone else noticed this? Is it just me?

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

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

campbellm SSC-Addicted Points: 445 More actions · Answer 1

i've just run the same query against our production server and get similar results !!!!!!!!!! I await a sensible explanation of this 'feature'. I am sure Microsoft will say upgrade to Yukon !

Mat Elkington Valued Member Points: 55 More actions · Answer 2

Well I guess it's not just me then, so that's something!

Makes querying the table a bit of a pain though, I believe a trip to Microsoft's SQL Server page may be in order...

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

Wow, I never noticed that. That's rather interesting and I have confirmed on multiple servers.

This "bug" does make the search for blank passwords much easier

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Chlijack SSC Rookie Points: 36 More actions · Answer 4

select on the master..sysxlogins table the password should be in the right place

Mat Elkington Valued Member Points: 55 More actions · Answer 5

Good point and very true.

Problem is though is that that table doesn't have all the information that I want...

Still, never mind...

mccork SSC-Insane Points: 22288 More actions · Answer 6

I think it's something in the character representation of the password that causes QA to throw a column when in GRID mode. When in column-aligned (CTRL-T) mode it lines up ok.

Also, by specifying all columns (instead of *) and "convert(varbinary(128), password)" instead of "password", they line up ok in both modes.

The thing that all my encrypted passwords share is the 0x0100 prefix. If I do a...

SELECT convert(sysname, 0x0100), * FROM master..syslogins

...I get even wackier grid mode results.

Cheers,
- Mark