SQL Server : Find User That Modified A View

Tags : auditingviews

The user modifying a view is automatically logged to the default trace. This script outputs the loginname of the last user to modify a view. Note that this data will not be available after the default trace rolls over.

Versions

SQL Server 2008+

SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'your_view'
       AND EventClass IN (46, /*CreatedObject*/  164 /*AlteredObject*/ )

    

Report Script

Top Users