June 1, 2008 at 1:21 am
Is there a way to find out when the password for a account like
'sa' was changed or reset or by whom? Please provide your inputs.
M&M
June 1, 2008 at 4:07 am
you can find out when it was changed using
select updatedate from syslogins where name = 'sa'
i dont think you can find out by who without having some kind of auditing trigger in place. maybe someone out there will prove me wrong tho 😛
June 1, 2008 at 5:16 am
I thought we couldn't this column updatedate all the time to check when the password was changed. It would change when any privilege was given to the login or if default db was changed etc. Don't you think?
M&M
June 1, 2008 at 5:17 am
Sorry typo.
I thought we couldn't use this column updatedate all the time to check when the password was changed. It would change when new privileges were added to the login or if default db was changed etc. Don't you think?
M&M
June 1, 2008 at 6:08 am
yes, your right. anything that affects the sa login would mean this column was updated. Hmm Plan B time
June 2, 2008 at 12:29 pm
Without having a DDL trigger in place or having an active server trace going at the time, you probably won't be able to determine exactly when and by whom. The master database is in simple recovery mode so there's not a good likelihood of being able to get anything from the transaction log, but if it is there, you would see when and possibly by whom.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 11:32 pm
I tried what you suggested in a test server. That is, changed the master to full recovery mode and then changed the sa password. Could you please help me identify how to detect who changed the sa password. I know it's me. 🙂 But help me identify the steps to confirm this. Thanks.
M&M
June 3, 2008 at 7:12 am
The best way, if you do this, is to use a log reader application, such as Apex SQL Log, for one. However, you can build a solution without putting your master database in full recovery mode. If it were me implementing a solution, I would work towards getting the trace up and running.
Since you're on SQL Server 2005, you can also place DDL triggers in place, but these aren't full-proof as a sysadmin can disable the trigger if he/she knows about it. However, it is possible to audit changes to logins using such triggers. I cover it in the book in my sig but the information on how to do that is readily available on the Internet if you do a few searches.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply