A join should work for this scenario:
declare @user table(
UserId varchar(50),
Username varchar(50)
);
declare @account table(
AccountName varchar(50),
CreateUser varchar(50),
ModifyUser varchar(50)
);
insert into @user values('User001','ABC');
insert into @user values('User002','XYZ');
insert into @user values('User003','PRQ');
insert into @account values('Account1','User001','User002');
insert into @account values('Account2','User002','User001');
select distinct
u.*
from @user u
inner join @account a
on u.UserID = a.CreateUser or u.UserID = a.ModifyUser
;