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...

As I stated in my comments, IF the queries are (almost) always for birthdays in a month / range of days, a clustered index on a base-dated dob should give...

There actually is, in theory, a fundamental difference between those queries:

The first query will drop/ignore any row that does not have a matching row in the Account table,...

I suggest you do it this way, which will make the output easier to use (at least for me):

DECLARE @command varchar(1000)
SELECT @command = '

It makes zero sense to pull out just the month and index it.  You'd be far better off indexing the date with a dummy year.

And if one (almost)...