Well I'd never use it as a common noun, only a proper one so I would always say 'He's not Superman', but yes, in your context 'a' is required.
To give another example:
"Is Bob good-looking?"
"Put it this way...
...he's not Brad Pitt."
...he's no Brad Pitt."
...he's not a supermodel."
...he's no supermodel."
If I were saying these sentences...I'd have to add something to the 'not' examples because they sound a bit strange on their own. I'd probably say 'Put it this way, he's not exactly Brad Pitt'. Likewise, 'Put it this way, he's not exactly a supermodel'. However, it shows where you need 'a' and where you don't.