How to select the largest value of the same column in multiple tables?
If you are using a DB that doesn't support CTEs (such as mySQL currently) or the query planner doesn't can't optimise what richardtallent's solution is trying to do so ends up scanning instead of seeking on user_id or event_timestamp (I suspect it MS SQL Server's planner may end up doing this), you could try unrolling the CTE a little and using it as a derived table:

SELECT MAX(et)
FROM   ( 
           SELECT MAX(event_timestamp) AS et FROM tableA WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableB WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableC WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableD WHERE user_id=@u
       ) AS dt


This should make it obvious to the planner that it can use indexes on the user timestamp columns (or better still, an index covering both) for each source table before unioning the four single row results and picking the max from there.

Of course if the query planner is smart enough to see how to perform richardtallent's version this more efficient way then which you chose comes down to which you find easier to maintain. The above may be hassle due to needing user_id=@u in several places making it impossible to use as a view in that form but it should be fine in a stored procedure or ad-hoc prepared query.