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.

Privileges needed to run SQL Server Profiler without sysadmin privileges

 

In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,

GRANT ALTER TRACE to [Login]

BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler

Automate SQL Server Profiler Trace

-- To create trace
exec @rc = sp_trace_create @TraceID output, 2, N'N:\SQLTrace\15FebTrace', @maxfilesize, NULL

## Note Option 2 here

--- To set trace status

exec sp_trace_setstatus @traceid, @status

where @status = 0 Stopped
1 Running
2 Closed, cleaned from server

---
To get the Traces running on the server

select * from ::fn_trace_getinfo(0)

---
To read trace content into a table
select * from ::fn_trace_gettable('N:\SQLTrace\15FebTrace.trc',default)

SQL Profiler buffers the trace data in temporary files on the client machine

SQL Profiler buffers the trace data in temporary files on the client machine, and if the drive on which these temporary files reside doesn't have ample free space, a Profiler trace (e.g. with the standard trace template) could deplete the drive of the free space rather quickly and end up disabling the trace.


System is low on disk space on drive 'C'. All SQL Server profiler functions are temporarily disabled.

The temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\Siva\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp. Upon exiting SQL Profiler, these temporary files would be removed automatically.

Work around:

To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.

You can use the following system stored procedures:

* sp_trace_create
* sp_trace_setevent
* sp_trace_setfilter
* sp_trace_setstatus

For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.

Reference:

An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance