I attended the SQL Data Camp in Chennai and received a RedGate book as a token of one the most active participant of the event.
Are you one looking to find answer for questions like below.,
· How to find who dropped a database?
· How to find who created the database?
· How to find who altered the database?
· How to find who altered the database configurations?
· How to find who dropped the schema?
· How to find who altered the schema?
· How to find who altered the server configuration?
· How to find who modified the login?
· How to find who modified the table?
If it is then, are you wondering is that possible on SQL Server?
Yes, SQL Server can beginning from version SQL Server 2005 onwards.
That is where SQL Server Default trace come into picture.
What is SQL Server Default Trace?
Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.
To get the default trace options
SELECT * FROM ::fn_trace_getinfo(default)
To know what is audited on default trace
SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description
FROM ::fn_trace_geteventinfo(1) Trc
JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id
Below is the screenshot “Schema Changes History” of Standard Reports at Server Level.
See that “it shows the event of Drop and Alter of the database
The same can be queried from SQL Server Default Trace like;
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2
Default trace can be queried to find answers on
Points to ponder