I am trying to find out who changed the password for a login in SQL Server 2008 R2.
I have already checked default trace – and it does not log that event. The default trace will include these security-related events:
/*
Audit Add DB user event
Audit Add login to server role event
Audit Add Member to DB role event
Audit Add Role event
Audit Add login event
Audit Backup/Restore event
Audit Change Database owner
Audit DBCC event
Audit Database Scope GDR event (Grant, Deny, Revoke)
Audit Login Change Property event
Audit Login Failed
Audit Login GDR event
Audit Schema Object GDR event
Audit Schema Object Take Ownership
Audit Server Starts and Stops
*/
Also, looked into transaction log backup to find that out, but no luck.
Is there any other way to find it out ?
Also, I am aware that a server side trace will help, but unfortunately in our server side trace, we did not include the Audit Login Change Password Event
.
Best article that I found is from Aaron Bertrand : Tracking Login Password Changes in SQL Server
Best Answer
My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up.
There is still hope, though. Let's say I did this:
This information is in the default trace under EventClass 104 (Audit Addlogin Event). However, if I change the password using either of these methods:
These events are not captured by the default trace, for obvious security reasons - it should not be possible for anyone with access to the default trace to figure out what someone else's password is, nor do they want to make it easy to even find out that a password has been changed (polling the frequency of these events, for example, can reveal certain properties of your security strategy).
So what else can you do? While this relies on the information still being in the log, and it also relies on using an undocumented DBCC command against a system database (you may wish to back up master and restore it elsewhere), you can get some information from the transaction log, e.g.:
This will yield, for the above two commands, rows with the following (partial) information:
Doesn't seem like much, but now take that 0x portion of the description, and then do:
Smoking gun! This is the person responsible for that event.
Of course, if they use
ALTER LOGIN
syntax for all operations (which they should be using instead ofsp_password
), you can't distinguish between someone changing the default database and someone changing the password. You also can't tell (at least that I can see) which login this affected, only that this person changed a login. Jon seems to think that this information is in the log as well, but I failed to find it (unlike the time information, which somehow I scrolled right past).There may be different answers for contained users in SQL Server 2012 - though I suspect password changes are still obfuscated in similar ways. Will leave that for a separate question.