June 29, 2020 at 9:10 pm
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
June 29, 2020 at 9:40 pm
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).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 30, 2020 at 8:47 am
Have you tried the default trace? That should capture changes to logins.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply