Finding out who changed the owner of a database when performing a restore

Question

A coworker who worked on restoring a backup changed the database owner from his name to 'sa' (rightly so) last night. While I had seen the his name in a SQL Server Management Studio window, I did not take a screenshot. He is now claiming that he did not restore that database.

I am an admin on the server and sysadmin on the database server. Is there a way to check who changed the owner of the database?

John Eisbrener · Answer 1 · 2019-08-01T15:12:17.777

I'm not aware of a way to find out who changed a db owner without some additional logging in place such as Auditing, but you can definitely tell who restored a database by checking the restorehistory table in the msdb database:

SELECT  *
FROM    msdb.dbo.restorehistory

If your coworker restored the database using impersonation they may be telling the truth as the database would be restored by the account being impersonated and this account would end up as the database owner. The impersonated user would show up as the user_name in the restorehistory table, so it'd be the first place I'd recommend you look.

Maybe someone else has a trick up their sleeve, though.

score 4 · Answer 2 · answered Aug 02 '19 at 02:34

You absolutely can see a running history of database ownership changes, as long as the information hasn't rolled out of the default trace (how long that takes depends on how busy your instance is in terms of other things that are captured by the default trace). Let's say I created (or restored) a database called splut:

CREATE DATABASE splut;
GO

Then I can change the owner in multiple ways, including through the SSMS UI, but let's do it in two ways from T-SQL:

EXEC splut.sys.sp_changedbowner N'sa';
GO
ALTER AUTHORIZATION ON DATABASE::splut TO sa;

We can see this information in the default trace (the basis for this query is from this canonical question, and there are other auditing columns you could potentially pull as well):

DECLARE @path NVARCHAR(260);

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

SELECT 
  LoginName, HostName, TextData, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (152) -- 152 = Audit Change Database Owner
ORDER BY StartTime DESC;

Results (note that sp_changedbowner actually generates the same ALTER AUTHORIZATION statement):

LoginName HostName TextData                                         StartTime
--------- -------- ------------------------------------------------ ----------
me\aaron  GRONK    ALTER AUTHORIZATION ON DATABASE::splut TO sa;    2019-08-01 22:26:58.243
me\aaron  GRONK    alter authorization on database::[splut] to [sa] 2019-08-01 22:26:38.677

In fact you could probably capture (some) information about the restore the user performed by changing the filter to:

WHERE EventClass IN (115, 152) -- 115 = Audit Backup/Restore Event

Finding out who changed the owner of a database when performing a restore

2 Answers2