Finding when SQL login switched to disabled status

  • webrunner

    SSC-Dedicated

    Points: 30273

    Hello experts,

    I just fixed an issue for a client where I discovered the SQL login in question was disabled. I forgot (my fault) to check the login data before re-enabling the login, so I think I lost the previous change date for the login.

    I did try to go back and restore backups of the master database to a different name in order to see if I could find a backup when the login was enabled, then disabled as of the next day's backup.

    I sampled a backup over a week old, then one a few days old, then the backup from this very morning. But the query below never returned the login I found disabled.

    select name, is_disabled
    from sys.sql_logins
    where is_disabled = 1

    Is there anything else out of the box that might help me find when this login was disabled? I know there is auditing I could add and I'll check to see if another DBA has added that auditing but just curious about

    (1) how this data is stored in master

    (2) is it incorrect to restore master under a different name on a different server to search for this data?

    Thanks for any help.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    I think that won't work unfortunately.  SQL is being helpful and pulling the data out of master when you query against sys.sql_logins.  You can verify this by switching to any database context and running the query you tried.  This is easy to verify though - make a new SQL login and see if it shows up in your renamed copy of master.  I am fairly confident it will.

    My understanding is that you would need to restore master over master in order to see the value for is_disabled on a login.

    As far as I know, SQL doesn't monitor for disabled logins.  But if the login was disabled due to multiple failed logins, you may be able to find something in the SQL log (presuming you are monitoring for failed logins).

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    Have you tried the default trace?  That should capture changes to logins.

    John

Viewing 3 posts - 1 through 3 (of 3 total)

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