I allow my instances to auto-parameterize, so if it's that valuable to the plan, presumably SQL will do so anyway.

Again, with dynamic SQL specifically -- which this code...

I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

You could try adding db_denydatawriter to the "guest" user.  Not sure it would work, but it should be worth a try.

It can't hurt to apply the limiting condition to both tables:

WHERE a.TransactionTime >= @startdate
and b.DateKey >= @startdate
and ...

Maybe since the DateKey is datetime, we should...

Are you willing to try changing the Clustering on table FctCoverageLengthDetail as I suggested earlier?  I'm just curious as to how much it would help.

Clustering table  FctCoverageLengthDetail on ( EnrollmentID, CoverageID ) might help, although by how much I can't tell, as the query is obviously very complex.

If you try that, then if...

SQL must parse the code before it runs.  At parse time, the column "RowNr" indeed does not exist because the code that adds the column to the table hasn't run...

You might be able to use the fact that the queries could produce different results as a cudgel to get your co-worker to stop writing his queries that way and...