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>