I would call a good survival horror game fun, if you enjoy being scared then you're going to have fun playing the game.
The cheif diffrence between say this and the godfather is that watching the godfather is a passive experience. When you are watching it the only thing you are focusing on is the plot and charecters etc. When you're playing a game, you're actully having to participate in it. At wich point that participation should be enjoyable.
I'm not saying it has to be fun in a bright colourfull flowers singing kind of way, but the gameplay of the game should be an enjoyable experience. The game should be able to stand on it's own with it's game play mechanisims and still be atleast a mildly enjoyable experience.
Take you're example of a survival horror game again. Look at say Silent Hill, the gameplay isn't perfect and it's deffintly been improved upon in later iterations, but at it's core it's still a solid third person shooter game with some cool puzzles. If the core gameplay, if running around as the main dude shooting monsters was repetitive, tiresome, frustrating etc then the game wouldn't work as well.
Look at Resident Evil, most people consider 4 to be the best in the series, because it finnaly nailed having gameplay that was actully fun as opposed to a great setting with horrible gameplay.
EDIT
A question. Do you mean fun, in the sense that you have a funny time and laugh, or in the sense of the game being an enjoyable experience, that doesn't bores you?
I mean that it should be enjoyable. Games deffintly don't need to be funny haha kind of thing, that'd be stupid.