Oddly enough, the games that scared me most as a kid were..

Castlevania 64 (had nightmares every time I played that game)
Rampage World Tour (for some reason that scientist lady freaked me out)
Glover N64 (the 'main overworld' at the beginning of the play session was kinda freaky)