How to find who dropped the database? Is that logged on SQL Server?

 

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

    JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id

Example:

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

Usage
Default trace can be queried to find answers on

1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
3. To find Login creation , modifications and failures
4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
5. Database file growth or shrinkage
6. DBCC operations

 

Points to ponder

1. Default trace is a Server Side trace and it is enable by default.
2. Default trace properties can not be modified, other than enabling and disabling of this trace.
3. Default trace by default enabled and stored on the “Log” folder
   
   
4. Default trace file will be created newly on start of the SQL Server service.
5. A single trace file can store  up to 20 MB, when it is full new file will be created.
6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.