Hello Anjan,
How are you doing sir?
You can use an OUTER JOIN as follows -


declare @SiteFullAccessFlag int = 1
declare @dimsite  table (sitekey int, site varchar(20))
declare @fact   table (id int, sitekey int)
declare @siteaccess table (sitekey int)

insert into @dimsite(sitekey, site)
values
(1, 'site 1'),
(2, 'site 2'),
(3, 'site 3'),
(4, 'site 4'),
(5, 'site 5')

insert into @fact (id, sitekey)
values
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5)

insert into @siteaccess (sitekey)
values
(1),
(2)

select * from @dimsite
select * from @fact
select * from @siteaccess

/* query */
select *
from @fact as f
inner join @dimsite as d
on f.sitekey = d.sitekey
left join @siteaccess as a
on f.sitekey = a.sitekey
where
(@SiteFullAccessFlag = 1) or
(@SiteFullAccessFlag = 0 and a.sitekey is not null)

/* In the case where the user has full access, if
you can change the logic to populate the
@SiteAccess table with ALL sitekeys instead of
keeping it blank, then the query would be a
simple INNER JOIN, which I guess would be faster
because a WHERE would not be needed

e.g. query */
select *
from @fact as f
inner join @dimsite as d on f.sitekey = d.sitekey
inner join @siteaccess as a on f.sitekey = a.sitekey