Alternative to triggers on system views for user auditing

Even writing the title I'm cringing at the idea I've tried this (but hey, I've learned something in the process).

I've got a problem with developers running migration scripts on the database and in the process overwriting stored procedures already existing in the database (DEV, thank a deity).

However, there's no way of finding out who made these changes because:

• they don't admit it

• SQL Server does not populate the principal_id column in the sys.objects system view, because everyone is connecting as dbo (more info here).

So, I've tried writing a trigger on sys.objects and that didn't work (I don't know why I ever expected it to) as I'm getting a error:


The object 'DEV_DB.sys.objects' does not exist or
is invalid for this operation.

I've also tried creating a user for each developer so that when they do their automatic migrations they will use the user I've made them and hopefully all changes made to sys.objects by that developer will be logged as the user they logged in with.

But that didn't work either, since principal_id still wasn't populated (what I have done was create a new login / user for each Developer & DB and give the login specific server roles : public, db_datawriter, db_datareader, db_ddladmin).

Any suggestions on what else I could try as an alternative?

You can always tap up the default trace to catch any changes to objects, including stored procs.

Give the following script a whirl, it's easy enough to find out who changed what and when. It's helped me catch out more than one sly developer ;)

SELECT  t.DatabaseName, t.ObjectID,t.NTUserName,t.HostName,t.StartTime, te.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL 
Server\MSSQL13.SQL2016\MSSQL\Log\log.trc', DEFAULT) t  
JOIN sys.trace_events te  on t.EventClass = te.trace_event_id  
ORDER BY t.StartTime DESC;

You'll need to change the path to wherever your SQL trace file is, to find out where it is run:

select path 
from sys.traces
where id = 1

One tip, remove the _number from the file name to read all available trace files e.g. if the file name is log_16.trc, change it to log.trc.

StackExchange Database Administrators Q#173707, answer score: 5