Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
I would set up one of Aaron's suggestions then back up the current password hash somewhere and then change the password back. See who screams .. or if it is just randomly changed back you then have the trace in place to catch them. –  Kenneth Fisher Jan 16 at 18:21
    
It's not entirely clear whether the password was changed to gain access or to prevent someone else's access. Just stating that because someone might not scream. Kin might also not know what the original password was. –  Aaron Bertrand Jan 16 at 18:38
    
The original password could be reset using the hash (ask me how I know haha), which should be somewhere in the transaction log. –  Jon Seigel Jan 16 at 18:43

3 Answers 3

up vote 9 down vote accepted

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:

CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF;

This information is in the default trace under EventClass 104 (Audit Addlogin Event). However, if I change the password using either of these methods:

ALTER LOGIN flooberella WITH PASSWORD = N'y';

EXEC sp_password N'y', N'z', N'flooberella';

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.:

DBCC LOG(master, 1);

This will yield, for the above two commands, rows with the following (partial) information:

Current LSN             Description
======================  ======================================================================
000000f2:000001b8:0002  ALTER LOGIN;0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000
000000f2:000001b8:0004  Alter login change password;0x01050000000000 ... same sid as above ...

Doesn't seem like much, but now take that 0x portion of the description, and then do:

SELECT name FROM sys.server_principals
  WHERE sid = 0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000;

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 of sp_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.

share|improve this answer
    
I think you could use fn_dblog/fn_dump_dblog against master (or a copy of it) to figure out which principal was changed, even if you have to spelunk using DBCC PAGE. –  Jon Seigel Jan 16 at 18:41
    
Look for the LOP_XACT_BEGIN for the Transaction ID you found. It will contain the exact time and the SID of the login that started it. –  Remus Rusanu Jan 16 at 18:42
    
@Jon you'd think so, but Page ID and Slot ID are NULL. –  Aaron Bertrand Jan 16 at 18:45
    
There has to be a way for SQL to know how to roll back the transaction... maybe it's just not exposing those values in the TVF even though they're actually there. –  Jon Seigel Jan 16 at 18:50
    
@Jon go ahead and take a look at DBCC LOG(master,3); (or the fn_dblog() equivalent) and see if you can spot anything that would help identify the target. When I do BEGIN TRANSACTION; ALTER LOGIN... I get even less useful information, which disappears if I roll back, and becomes the above if I commit. –  Aaron Bertrand Jan 16 at 19:00

this is longer than a comment, posting as answer

select top(10) 
    [Transaction ID], 
    [Begin Time], 
    [Transaction Name], 
    [Transaction SID],
    SUSER_SNAME([Transaction SID])
from fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT';

Transaction ID Begin Time               Transaction Name                  Transaction SID
-------------- ------------------------ --------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0000:00002b12  2014/01/08 20:10:14:890  Event_Session_Startup             NULL
0000:00002b13  2014/01/08 20:10:15:027  DBMgr::StartupDB                  NULL
0000:00002b14  2014/01/08 20:10:15:513  AddGuestUserToTempdb              NULL
0000:00002b15  2014/01/08 20:10:15:537  DBMgr::StartupDB                  NULL
0000:00002b16  2014/01/08 20:10:15:537  DBMgr::StartupDB                  NULL
0000:00002b17  2014/01/08 20:10:15:537  DBMgr::StartupDB                  NULL
0000:00002b18  2014/01/08 20:10:15:540  DBMgr::StartupDB                  NULL
0000:00002b19  2014/01/08 20:10:15:550  DBMgr::StartupDB                  NULL
0000:00002b1a  2014/01/11 11:49:42:760  AutoCreateQPStats                 0x010500000000000515000000A065CF7E784B9B5FE77C877084B65600
0000:00002b1b  2014/01/11 11:53:26:620  test_ack                          0x010500000000000515000000A065CF7E784B9B5FE77C877084B65600

(10 row(s) affected)
share|improve this answer
    
+1 My bad, I looked right past that. –  Aaron Bertrand Jan 16 at 21:25
1  
@RemusRusanu This will be only useful if you are directly querying the whats in the T-log, but if you try to read from a T-log backup, the SID's would be chopped off. Also every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will never go away and never be reused. –  Kin Jan 16 at 21:40

You can utilize DDL trigger on server level (note that for this example you must have SQL Server Database Mail feature enabled and set):

CREATE Trigger [Trg_TrackLoginManagement]
on ALL Server
for DDL_LOGIN_EVENTS
as
set nocount on
declare @data xml,
          @EventType varchar(100),
          @EventTime datetime,
          @ServerName varchar(100),
          @AffectedLoginName varchar(100),
          @WhoDidIt varchar(100),
          @EmailSubject varchar(500),
          @EmailBody varchar(800),
          @EmailRecipients varchar(300)
set @EmailRecipients = 'name@domain.com'
set @data = eventdata()
set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
set @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
set @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
set @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')

set @EmailSubject = 'ALERT: DDL_LOGIN_Event: ' + @EventType + ' occured by ' + @WhoDidIt + ' on ' + @ServerName

set @EmailBody =  'DDL_Login_Event: ' + @EventType + char(10) + 
                 'Event Occured at: ' + convert(Varchar, @EventTime) + char(10) + 
                 'ServerName: ' + @ServerName + char(10) +
                 'Affected Login Name:      ' + @AffectedLoginName + char(10) + 
                 'Event Done by: ' + @WhoDidIt
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @EmailRecipients,
    @body = @EmailBody,
    @subject = @EmailSubject ;
GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.