Alternative to triggers on system views for user auditing

Question

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:

1. they don't admit it
2. 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?

Answer 1

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.

Answer 2

If the developers have individual logins, you might consider a DDL Trigger. Here's an example for the CREATE_PROCEDURE, ALTER_PROCEDURE, and DROP_PROCEDURE events.

USE YourDatabase;
GO

CREATE TRIGGER trgStoredProcDDL
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    --Get relevant info from EVENTDATA()
    DECLARE @Login SYSNAME= EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME');
    DECLARE @DBUser SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME');
    DECLARE @Schema SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
    DECLARE @Proc SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

    --Optional: error message for end user.
    RAISERROR ('Please stop!', 16, 1);

    --Optional: Rollback transaction that fired the DDL trigger
    ROLLBACK;

    --Optional: log some data to a table.
    BEGIN TRAN
        INSERT INTO guest.LogTable(LogMsg, LogDate, [Login], [User])
        VALUES('Stored Proc was created/altered/dropped: [' + @Schema + '].[' + @Proc+ ']', 
            CURRENT_TIMESTAMP,
            @Login,
            @DBUser);
    COMMIT

    --Optional: send an email/alert.
    DECLARE @Subj NVARCHAR(255) 
    SET @Subj = @@SERVERNAME + ' - Stored Proc created/altered/dropped'
    EXEC msdb..sp_send_dbmail
        @recipients = '[email protected]', 
        @subject = @Subj,
        @body = 'Your message...'
END;

Here's a blog post about handling events with DDL triggers with some further explanation: SQL Server Event Handling: DDL Events