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

share|improve this question
    
I'm guessing there may be some sort of auditing that needs to be turned on/configured first. Sorry if this is a newbie question. I usually only deal with SQL Server from a Developer's perspective. –  David Stratton Sep 19 '12 at 15:52

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
Thank you! I['s not returning any results, when running against the master database, or another database on the server. I changed the principal that owns the DBO role and re-ran the query, and still no results. Is it possible my trace just isnt' turned on, or something is configured properly? –  David Stratton Sep 19 '12 at 17:47
    
@DavidStratton I'll confess I was only able to test on SQL Server 2012 due to VM availability. It's possible that this specific event is not included in the default trace prior to SQL Server 2012 (you can check this in the trace-related catalog views); it's also possible that you shut the trace off. –  Aaron Bertrand Sep 19 '12 at 17:50
    
Thank you! I eliminated the WHERE clause and got results, so I think the trace is turned on. I'll look deeper on MSDN and see if I can find more info. This pointed me in the right direction, at least. –  David Stratton Sep 19 '12 at 17:52

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.