CODEWITHSUNDEEP
sql-serversql-server-2008-r2dbo
user1683507
user1683507

Reputation: 51

How to find the details of a DBO change on a database?

I would like to run a query to view when and by whom the DBO of a particular database was changed. How would I do that?

Upvotes: 5

Views: 518

Answers (3)

CR241
CR241

Reputation: 2623

You can get that info from the default trace.

Here's an article with sample queries of the trace: Default Trace

You can also see the most recent changes in SSMS by right-clicking on the database -> Reports -> Schema Changes History -> Schema Changes History.

via TSQL you can use:

SELECT StartTime
       ,LoginName
       ,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )

Upvotes: 0

user847990
user847990

Reputation:

In reference to your comment on explaining more about the default trace file...

When you query sys.traces it is going to return a record for each trace that is currently running on your SQL instance. One of those records should reference the default trace file if the feature is enabled. The file format of the defaul trace file is log_000.trc. The number is a sequential number. The file is by default stored in the LOG directory where your SQL ERRORLOG files are kept, and it cannot be changed.

If you go to that directory you are likely to find multiple trace files for your default trace. The default trace will create a new file once a file reaches 20MB I believe. Which since the default trace does not capture to much information it generally does not create a large number of files. Although I will note that it is something that should be monitored, systems that go untouched for long periods of time can have these little files pile up and cause the storage to fill up.

It looks like the current trace file does not have the event you are looking for in it, which leads to it was prior to that file. So you have two options of either running the sys.fn_trace_gettable() query on each file individually; or I would take the older files and copy them down to a client computer or something besides my production server and just open each one up in profiler, depending on how many we are talking about. If the event in question caused your application to break you can obivously reference that time to find the files you need to focus on.

Upvotes: 0

user1683507
user1683507

Reputation: 51

I found this info on stackexchange... and tried it and it worked. The only problem was that it only gave me the user who changed the DBO the last time, not the time before that. I was the person who changed the DBO the last time because my application was not working. I need to know who changed the DBO the time before that one. Does anyone know how I would do that - find the last couple of times the DBO was changed and by whom? This is the code that let me know who changed it the last time:

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.

Upvotes: 0

Related Questions