I recently had a situation where SQL server logins were getting deleted from the system and nobody could figure out why or by whom.   So in this post, we present a few ways to catch this.

First, if you are using SQL Server 2008, the easiest method is to capture this via the new Auditing features by using a Server Audit Specification on the SERVER_PRINCIPAL_CHANGE_GROUP.

Understanding SQL Server Audit
http://msdn.microsoft.com/en-us/library/cc280386.aspx

If you are using SQL Server 2005 (or want to use a different method in SQL Server 2008), then read on.  We can do this by reviewing the default trace log and looking for the Audit AddLogin Event with an EventSubClass of 2 (Drop).  

Reading the default trace into a temp table (#trace) using fn_trace_gettable, we can query and get the event we are looking for (Audit Addlogin Event with an EventSubClass of 2):

select e.name, t.EventClass, t.EventSubClass, t.LoginName, t.StartTime, t.DatabaseID from #trace t
    inner join sys.trace_events e
    on t.EventClass = e.trace_event_id
    order by StartTime desc

image 

Alternatively, you can define your own trace and capture the Audit Server Principal Management Event and look for an EventSubClass of 3 (Drop):

image

Finally, if for some reason a trace doesn’t work for you (as was the case with us) – you can define a DDL trigger to insert information about the events into a table.  In this situation, we defined the structure of a global temporary table using a SELECT INTO statement and then created our trigger.  First the table structure:

select * into ##LOGIN_WATCH
      from (
      select r.*, s.login_name, s.host_name, EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS 'CmdText'
      from sys.dm_exec_requests r
         inner join sys.dm_exec_sessions s
         on r.session_id = s.session_id
         where r.session_id = @@SPID) x

This will create a global temp table that will then have the insert operation logged into it – we can ignore the first entry….  Now to create the trigger:

create trigger [no_dropped_logins]
on all server
for drop_login
as
  insert into ##LOGIN_WATCH
    select r.*, s.login_name, s.host_name, 
   
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS 'CmdText'
     from sys.dm_exec_requests r
       inner join sys.dm_exec_sessions s
       on r.session_id = s.session_id
       where r.session_id = @@SPID

GO

Any operation that runs a DROP LOGIN command will trigger this to log information into that global temporary table.

NOTE: In this approach, you must be careful to ensure the global temp table exists first.  You can of course easily create a permanent table for use with this, but we only needed this trigger for very short period of time as this was happening very regularly.

An interesting side effect is that with the DDL trigger in place, and the global temporary table *gone*, you are no longer able to drop logins since the trigger fails and cancels/rolls back the transaction.  That could provide a creative way to prevent this from happening without modifying security rights (since we didn’t know who was dropping the logins).

- Jay