01 January,2013 by Jack Vamvas
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.
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 ;