A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local table alias to make sure you are referencing a column in the local table.

Edit: That is:

select s.* from #Security s where s.[Security Id] in (select distinct sms.[Security Id] from #SystemMappedSecurity sms)

Then you'll get the error you expect, and can correct it.