SQL Server – Find who made a database security change

A nightly SQL Server Agent batch job started failing suddenly , with a message about a database user missing.

The SQL Server Agent job history indicated the job ran successfully for 6 months. Either someone had restored the database to a much older version or a database user was deleted.

The SQL Server default trace has an event - Audit Add DB User  .  The event is recorded in the trace when a login is added\removed as a database user.

This query returns all events of the Audit Add DB User . Luckily , the trace file hadn’t yet rolled over. Keep in mind the SQL Server default trace file has 5 trace files @ 20 MB each. If the system is very busy , these files can roll over quickly

 Using the query – I was able to discover the TargetUserName, database name , time of change and under what login name the change occurred.

Script to find who made a SQL database security changes

SELECT  te.trace_event_id,
TE.name AS [EventName] ,
T.EventSubClass,
T.TargetUserName,
        T.DatabaseName ,

        t.DatabaseID ,

        t.NTDomainName ,

        t.ApplicationName ,

        t.LoginName ,

        t.SPID ,

        t.Duration ,

        t.StartTime ,

        t.EndTime
       

FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) T

        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

WHERE   t.StartTime > GETDATE() -1
    and te.name  =   'Audit Add DB User Event'  
   

ORDER BY t.StartTime ; 

 Read More on SQL trace events and SQL Server Security

SQL Server – default trace FAQ - SQL Server DBA

SQL Server – Audit Backup\Restore Event with SQL Default trace ...

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA