Find out when/who dropped an object without auditing

If your environment doesn’t have any kind of auditing and you need to find since when an object was dropped.

If you have the default trace enabled (good practice) you can track when the object was dropped and determine if that was the root cause for some performance issue for example.

The point here is to find the root cause of an issue, not point the finger to who did it 😉

First, let’s check what kind of events the default trace has

SELECT DISTINCT Trace.EventID
        , TraceEvents.NAME AS Event_Desc
 FROM ::fn_trace_geteventinfo(1) Trace
 JOIN sys.trace_events TraceEvents ON Trace.eventID = TraceEvents.trace_event_id 

You are going to see the events

The next step is get the default trace path

SELECT path
 FROM sys.traces
 WHERE is_default = 1;

Knowing the path you can get all the files name using xp_cmdshell for example

EXEC xp_cmdshell 'dir e:\"Program Files"\"Microsoft SQL Server"\"MSSQL10.MSSQLSERVER"\MSSQL\*.trc'

Grab the file name you want and change the script below

SELECT 
    CASE EventClass
        WHEN 46 THEN 'Object:Created'
        WHEN 47 THEN 'Object:Deleted'
        WHEN 164 THEN 'Object:Altered'
    END
    , DatabaseName
    , ObjectName
    , HostName
    , ApplicationName
    , LoginName
    , StartTime
 FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\your_file_name.trc', DEFAULT)
 WHERE 
    EventClass IN (46,47,164) 
    AND EventSubclass = 0 
 ORDER BY StartTime DESC

In my scenario what is showing is the creating object in the bottom with some index creation, drop index, object alter (it’s not telling what kind of change), create a new object and alter (the primary key creates the index and alter the table adding the constraint).

Plus you can see the login name and when the action was made. 😉

How to add Active Directory powershell module

I’m going to show the steps how to add the AD module in your machine and in the server if you need to get information like run Get-ADPrincipalGroupMembership to know what groups a specific user belongs.

To add this module in your local machine is quite easy. Install the Remote Server Administration Tools for Windows 10 package

But, to install it in a Windows Server for example you need to add this feature following the steps .

  1. Open Server Manager Dashboard
  2. Click Manage -> Add Roles and Features Wizard
  3. Click next until to show the picture below in the item features

After install it (no restart needed) you can run import-module ActiveDirectory and run something like this:

Get-ADPrincipalGroupMembership -Identity 'user_name' | Select-Object name

$groups = Get-ADGroup -Filter {name -like 'user_group'} -Properties * | Select -property name

foreach($group in $groups) {
    if($group.name -ceq 'Group trying to find') {
        $group.name
        Get-ADGroupMember -Identity $group.name | Where-Object objectClass -Like 'group' | Select-Object name
    }
}