Every Villain has an ulterior motive, simply because he/she/it has to, the villain must have goal to accomplish and a reason for completing that goal.
A game where a guy/girl/thing gets up one morning and says "You know what? Today, I'm going to do my best to kill everyone on the planet!" would be fun to play for shits and giggles when you're bored. The most similar game I can think of at the moment is Castle Crashers.
In my own personal opinion, the best villain in gaming is probably Arthas Menethil (better known in World of Warcraft as The Lich King) from the Warcraft 3 series. He was the original Hero of the game, turned evil by lust. Yes, he did ultimately want to kill everything, and it was a fun game to play with a great story.
Getting back on-topic, Games without story won't last long. War-game players love Halo and/or Call of Duty because they have a campaign with a decent story (personal opinion) that appeals to the players.
A game has to be able to answer questions like "Why am I killing these people?" or "Why are these people trying to kill me?"
A game also must consist of some drama. Life is drama. People like something that they can relate to or understand. You know that (usually) there is a hero and a villain. You also (usually) know that the hero will overcome the villain's obstacles no matter what. Drama is the way those events are played out. If it was just a game where a guy steals your bagel, and immediately after he does it you kill him and take your bagel back, it would be entertaining once or twice (I mean come on, it would at least be humorous to play a few times) but it will never make it in the industry.
Edit: Also, a game with no emotional impact? That isn't a game, that's a program. I'm not being biased to games with intricate stories, just saying that no matter what the game is it should invoke emotional responses. Whether that response be anger, love, curiosity (if you can call that an emotion), joy, sadness etc., is besides the point because it is an emotional response.