SQL Server: How to Find which user deleted the database in SQL Server
Related Topics: SQL Server Replication Tips>
Next Topic: SQL Server Indexing Articles & Tips
In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server.
Steps to find who deleted the user database in SQL Server
1. Open SQL Server Management Studio and Connect to the SQL Server Instance.
2. Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet.
3. This will open up Scheme Changes History report which will have the details about who deleted the SQL Server Database along with the timestamp when the database was deleted. Refer the below snippet for more information.
Identify who deleted the user database using TSQL script
The SQL Server Default Trace file gives very useful information to a DBA to understand what is happening on the SQL Server Instance. For more information, see Importance of trace files in SQL Server.
Execute the below query to find the default path of trace file in SQL Server.
SELECT path AS [Default Trace File] ,max_size AS [Max File Size of Trace File] ,max_files AS [Max No of Trace Files] ,start_time AS [Start Time] ,last_event_time AS [Last Event Time] FROM sys.traces WHERE is_default = 1 GO
Thank you for taking your time to read
this article. Let's be Connected....
How to Load SQL Server Trace File in SQL Server Table
Execute the below script to load the default trace file content in a temporary table to read the relevant information with respect to who deleted the user database on the instance of SQL Server. If you don't find the relevant information in the latest trace file then it is recommended to load the data from all the available trace files on the server to explore the information.
USE tempdb GO IF OBJECT_ID('dbo.TraceTable', 'U') IS NOT NULL DROP TABLE dbo.TraceTable; SELECT * INTO TraceTable FROM ::fn_trace_gettable ('G:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\log_12.trc', default) GO SELECT DatabaseID ,DatabaseName ,LoginName ,HostName ,ApplicationName ,StartTime ,CASE WHEN EventClass = 46 THEN 'Database Created' WHEN EventClass = 47 THEN 'Database Dropped' ELSE 'NONE' END AS EventType FROM tempdb.dbo.TraceTable WHERE DatabaseName = 'MyTechMantra' AND (EventClass = 46 /* Event Class 46 refers to Object:Created */ OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */ GO
From the above snippet you could see that the event class 46 represents the database creation time along with the user who created it and event class 47 represents the database deletion time along with the user who deleted the database.
In this article we have seen how easily one can find out who deleted the user database in SQL Server with the help of in built SQL Server Schema Changes History Report.
- SQL Server Replication Tips and Articles for DBAs and Developers
- SQL Server Indexing Tips and Articles for DBAs and Developers
- How to Configure and Use Transparent Data Encryption in SQL Server
- How to Configure and Use Backup Encryption in SQL Server 2014
- Different Types of SQL Server Recovery Models
- Different Types of Database Backups supported in SQL Server
- Permissions Required to Perform Database Backup in SQL Server
- How to Perform FILEGROUP Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform TAIL-LOG Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: April 18, 2015
Please leave your Valuable Comment or Let us know how this article helped you: