CODEWITHSUNDEEP
sql-server
S V
S V

Reputation: 125

How to determine who performed DROP/DELETE on Sql Server database objects?

There is always a need to find out details, either intentionally Or mistakenly someone executed DROP/DELETE command on any of following SQL Server database objects.

  1. DROPPED - Table from your database
  2. DROPPED - Stored Procedure from your database
  3. DELETED - Rows from your database table

Q. Is there TSQL available to find db user who performed DELETE/DROP?

Q. What kind of permissions are needed for user to find out these details?

Upvotes: 2

Views: 2371

Answers (1)

Prabhat G
Prabhat G

Reputation: 3029

Did you check this ? Right click on database. Go to as shown in image : sql server

Solution 2 : This query gives alot of useful information for a database(apply filter as required) : 

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       --gt.SPID, 
      -- gt.EventClass, 
       te.Name AS EventName,
       --gt.EventSubClass,      
      -- gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
       gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164) --AND gt.EventSubClass = 2
ORDER BY StartTime DESC;

Upvotes: 3

Related Questions