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_idcolumn in the
sys.objectssystem 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 :
Any suggestions on what else I could try as an alternative?