A problem with the SYSLOGINS view?

Howdy.
While attempting to write a stored proc, on a SQL Server 2000 machine running win2k, that would back up all the user logins, I noticed something a little odd in the syslogins view. I was wondering if anyone else has this problem.

Make sure you have a non NT login with a password on your server and you are viewing your results in query analyzer in grid mode.

do a Select * from master..syslogins

Now, look at the password column. On mine, if the password is not null, then it makes the field blank and bumps the rest of the results over one. So the in the language column, I see the encrypted password, and in the deny login column I see the default language... anyone else noticed this?Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.|||Q1 Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.
--A1 This should fix it:
ALTER VIEW syslogins AS SELECT
sid = convert(varbinary(85), sid),
status = convert(smallint, 8 +
CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END),
createdate = convert(datetime, xdate1),
updatedate = convert(datetime, xdate2),
accdate = convert(datetime, xdate1),
totcpu = convert(int, 0),
totio = convert(int, 0),
spacelimit = convert(int, 0),
timelimit = convert(int, 0),
resultlimit = convert(int, 0),
name = convert(sysname, name),
dbname = convert(sysname, db_name(dbid)),
password = convert(varbinary(256), password),
language = convert(sysname, language),
denylogin = convert(int, CASE WHEN (xstatus&1)=1 THEN 1 ELSE 0 END),
hasaccess = convert(int, CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END),
isntname = convert(int, CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END),
isntgroup = convert(int, CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END),
isntuser = convert(int, CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END),
sysadmin = convert(int, CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END),
securityadmin = convert(int, CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END),
serveradmin = convert(int, CASE WHEN (xstatus&64)=64 THEN 1 ELSE 0 END),
setupadmin = convert(int, CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END),
processadmin = convert(int, CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END),
diskadmin = convert(int, CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END),
dbcreator = convert(int, CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END),
bulkadmin = convert(int, CASE WHEN (xstatus&4096)=4096 THEN 1 ELSE 0 END),
loginname = convert(sysname, name)
FROM sysxlogins WHERE srvid IS NULL|||Yeah, changing the datatype to varbinary did fix it, however, being the system view that syslogins is, it wont let me save the changes. I ended up doing a select from the sysxlogins, decyphering the xstatus bit info, and scripting that out. Bummer that the view is broken though. It's good to know that I am not the only one with the problem.|||Try making sure you are logged in as dba, enable ad hoc system table updates, etc.?|||Yeah, I tried that... logged in with sa, enabled system table modification... I think maybe because it is a view instead of a table, it didnt like it. Anyway, no matter, my work around should function nicely.|||Odd, the syslogins view alteres each time I've attempted it.|||Well, enterprise manager let me alter the view. No idea why I couldnt do it with a script through query analyzer.|||You might want to check the Server connection properties in EM (just to see if you are using the same account you had attempted to use in isqlw, a.k.a. QA).|||This is strictly a visual problem (under very specific circumstances - not windows login using the grid in qa) - not a problem for your original task - making a backup - correct ?|||Well, ok, the original problem was as follows. One of our SQL servers here at work contains back end databases for customers websites. As you can imagine there are quite a few databases. (Over 1000) Each customer has a SQL login with access granted to their appropriate database. That login information is saved, as you know, in the sysxlogins table in the master database. Now in our back up schema, master isnt backed up that often, so we needed a method of backing up the login info. My original thought was to take the information from syslogins and script it into a table in a database that is backed up every 15 minutes. This is needed because on average 3 to 5 new databases can be added per day, and 2 to 3 deleted. When I went to select the records from the syslogins, I realized that the view had the bug and wanted to make sure that it was everywhere and not only on my machines.

As for the solution, I pull the login data directly from sysxlogins table and populate my Login_Info table. And all is happy. :D|||I just wanted to make sure - I have been using this view without problems - it retrieves and stores the data appropriately. Only through qa in grid mode does this problem appear.

A problem with SQL Integrated Windows Logins

I am working with 3 SQL 2000 Machines. I manage them by assigning Windows 2000 groups as logins. All of this is fine except that on one server, I belong to a Windows group which gives me certain rights.
I log into my Windows 2000 machine using my domain account which is in the Windows group, When I go into Enterprise Manager I get a message "unable to access default database" and I can not log in. If I put my Windows account into the SQL logins everything is OK. No one else has this problem and I only have it on one server. Anyone?Check the domain account default database. Maybe the account does not have access rights for the default database. (if not then map the account to that DB,or change the default database for another one which have the appropriate rights).
When you log in using Enterprise Manager, SQL checks only the login permission not the access rights, that's why the connection is allowed.

Originally posted by courtjs
I am working with 3 SQL 2000 Machines. I manage them by assigning Windows 2000 groups as logins. All of this is fine except that on one server, I belong to a Windows group which gives me certain rights.
I log into my Windows 2000 machine using my domain account which is in the Windows group, When I go into Enterprise Manager I get a message "unable to access default database" and I can not log in. If I put my Windows account into the SQL logins everything is OK. No one else has this problem and I only have it on one server. Anyone?|||Check with SP_HELPLOGINS.