Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2002
    Posts
    63

    Unanswered: 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?

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    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.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    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

  4. #4
    Join Date
    Dec 2002
    Posts
    63
    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.

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    Try making sure you are logged in as dba, enable ad hoc system table updates, etc.?

  6. #6
    Join Date
    Dec 2002
    Posts
    63
    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.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Question

    Odd, the syslogins view alteres each time I've attempted it.

  8. #8
    Join Date
    Dec 2002
    Posts
    63
    Well, enterprise manager let me alter the view. No idea why I couldnt do it with a script through query analyzer.

  9. #9
    Join Date
    Oct 2002
    Posts
    369
    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).

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  11. #11
    Join Date
    Dec 2002
    Posts
    63
    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.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •