DDL trigger to get username in SQL Server

Question

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

Answer 1

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