Just wondering why you would populate said temp table with 0 records when the user has access to ALL sites.   If you simply copied all the PK values for the site dimension into your temp table when a user has access to all, and only the sites they have access to when their access is limited, into such temp table; and then index that temp table; I'm not sure why you'd have a problem.   Also, have you tried the alternate form of an IN, which is to use EXISTS ?   Sometimes that performs better.

Post an execution plan as a .sqlplan file and someone here can probably help you figure out where the problem is.

EDIT: grammar fix for clarity.