• There are a number of tools you can set up to track changes to the system. If you want to track individual queries you can use extended events or trace. If you want to see data tracked you can set up change data capture. As was already said you can set up triggers to capture modifications.

    But, you're right. There is no way to differentiate a given user if everyone is using a common logon. If you can modify the application code so that it has to also pass in the user name, then you could capture that using one or more of the mechanisms above. But, barring that, you're going to be largely stuck.

    Architectural choices really do matter. Choosing to use a SQL login absolutely takes away certain types of functionality.

    Hmmm... you can capture the client host name. If a person is connecting from their own machine, you could get that at least. But if they're connecting through a server that won't help either.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning