Search code examples
sql-servert-sqltriggersaudit-logging

DDL trigger to get username in SQL Server

I have a "who-did-what-to-which-table-and-when" requirement from one of my clients. Brief description of the system is as follows:

  • No source code of the software
  • No tracking of events by the software
  • All users use the application under the "sa" login
  • There is a login table in the database, that verifies the user credentials during application startup
  • SQL Authentication, not Windows Authentication. Creating separate users/roles in SQL is not possible as the connection string from the front-end to the back-end is hard-coded and cannot be edited

My issue is as follows:

  • I can get HostName, IP,Suser_Name(),MAC address etc. But the client isn't happy with any of them
  • I can map IP addresses to the user login table. Client isn't happy again.

My Question is:

Is there anyway I can link the username from the Login table to the DDL trigger? The Login table has 2 columns, Username and Password. Is there some join/ any other operation that I'm possibly overlooking here?

System works on SQL Server 2008

Solution

  • You might be able to trace the select statement of the user/password table and then join on spid and time frame.

    https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace

    • Create a trace by using sp_trace_create.
    • Add item events with sp_trace_setevent. Batch Complete should work in this case.
    • Set a filter with sp_trace_setfilter. Filter it down to your user/password table.
    • Start the trace with sp_trace_setstatus.
    • Stop the trace with sp_trace_setstatus.
    • Close the trace with sp_trace_setstatus.

    https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/create-and-run-traces-using-transact-sql-stored-procedures