Determining which applications are writing to a given table in SQL Server can be approached in several ways, depending on your access level, the tools you have at your disposal, and whether you need to monitor this activity in real-time or over a period. Here are some methods to consider:

1. SQL Server Profiler / Extended Events

SQL Server Profiler and Extended Events are tools for monitoring SQL Server database engine instances. You can use them to track queries executed against a specific table. By filtering the events for INSERT, UPDATE, or DELETE statements against the table in question, you can identify which applications are making these changes. The application name is usually captured as part of the session information.

• SQL Server Profiler: This tool is more user-friendly but considered to be legacy in newer versions of SQL Server. It’s easier to set up for quick checks but can add significant overhead to the server if not used carefully.
• Extended Events: This is the recommended approach for monitoring in newer versions of SQL Server. It’s more flexible and less resource-intensive than SQL Server Profiler. You can create an event session that captures write operations on your table and includes the application name.

2. SQL Server Audit

SQL Server Audit can track and log a wide array of events in SQL Server. You can configure an audit to monitor access to specific objects, such as a table, including write operations. SQL Server Audit records can include the application name, making it possible to identify which applications are writing to the table.

3. Query Store

If you’re using SQL Server 2016 or later, Query Store collects detailed performance data about your queries. While its primary use is for performance tuning, you can analyze the data to find queries that modify a specific table. This method might not directly show the application name but can lead you to the queries of interest, which you can then trace back to specific applications based on their patterns or execution context.

4. Triggers

Although not generally recommended for this purpose due to potential performance impacts, you can create triggers on the table for INSERT, UPDATE, and DELETE operations. These triggers could log information about the modifications, including the application name (using APP_NAME() function) and other session context information to a logging table. This approach is more invasive and should be used judiciously, especially on tables with high write volumes.

5. System Dynamic Management Views (DMVs)

DMVs such as sys.dm_exec_sessions and sys.dm_exec_requests can provide information about currently executing requests, including the application name. By querying these DMVs, you can sometimes catch write operations in real-time and identify the application responsible. This approach is more suited for ad-hoc monitoring rather than continuous tracking.

Choosing the Best Approach

The best approach depends on your specific needs:

• For real-time monitoring, Extended Events or SQL Server Audit are recommended.
• For historical analysis, Query Store (if available) or SQL Server Profiler/Extended Events with historical session data analysis might be best.
• For detailed, real-time tracking with context, consider using Triggers with caution due to potential performance impacts.

Always consider the performance implications of these monitoring techniques, especially in production environments.