Long time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it possible to find who dropped login from server?” Initially I check the “Schema Change History” report but it was not showing expected output. I went ahead and looked into default trace and found that there are some entries when I delete a login. Since the data was available in default trace, I have written below script to read those files and show them in readable format.

DECLARE @enable INT

SELECT TOP 1 @enable = convert(INT, value_in_use)
FROM sys.configurations
WHERE NAME = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN
    DECLARE @d1 DATETIME;
    DECLARE @diff INT;
    DECLARE @curr_tracefilename VARCHAR(500);
    DECLARE @base_tracefilename VARCHAR(500);
    DECLARE @indx INT;

    SELECT @curr_tracefilename = path
    FROM sys.traces
    WHERE is_default = 1;

    SET @curr_tracefilename = reverse(@curr_tracefilename)

    SELECT @indx = PATINDEX('%\%', @curr_tracefilename)

    SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) - @indx) + '\log.trc';

    SELECT TargetLoginName 'Login which was dropped'
        ,StartTime 'When'
        ,LoginName 'who dropped'
        ,ApplicationName 'from where'
        ,HostName 'machine name'
    FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
    WHERE EventClass IN (
            104                    -- SQL Login would have "Audit Addlogin Event"
            ,105                -- Windows Login would have "Audit Login GDR Event"
            )
        AND EventSubclass = 2 --drop
END
ELSE
Select 'Default trace is not enabled'

Hope this would help some DBA to find culprits and punish them.

<Image taken from http://www.chasing-joy.com&gt;

• Cheers,
• Balmukund Lakhani
• Twitter @blakhani
• Author: SQL Server 2012 AlwaysOn – Paperback, Kindle