How to tell when DBO was changed (and if possible, by whom)

Question

I am looking for a way to track when the DBO account is changed. For example, if someone uses sp_changedbowner is there a way, after the fact, to determine when this occurred, and even better, what user was used to make the change?

This would be useful in tracking changes in the event of a database breach where teh attacker changed permissions, for example, so I suspect the possibility must be there.

I've been attempting to google it and look for it in the MSDN documentation, but I'm just not finding this.

Answer 1

This is in the default trace. Whether you use sp_changedbowner or the proper, modern syntax, it will show up as the latter:

alter authorization on database::[foo] to [bar]

You can retrieve the information this way:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT * -- whittle down to the meaningful trace columns
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 152
ORDER BY StartTime DESC;

Now, the event might have happened a long time ago, so it won't necessarily still be in the trace.