Sure they can. Rebel Galaxy, a game where you customize your own space ship and has an intricate in-game economy, was a lot of fun for me. No Man's Sky, despite all its faults, was also a pretty fun game to explore around.
They could definitely be good. I personally enjoy space games that are centered around exploration. Seeing what kinds of worlds people can create without the limitations of Earth biology is always interesting to me. Your suggestion of a Sims-like game sounds like it has some potential. Though the main challenge would be providing a unique enough experience to justify being set in space while not veering into another genre entirely. If someone ever manages to pull that off I would love to see it.
Depending how you look at it, there are already Sims-esque games out there, some fairly well acclaimed, others haven't really caught on. For example, Rimworld which is a survival sandbox but also has elements to it which you can consider to be like the Sims, such as having different types of relationships with each other. Planetbase is another one, similar again to Rimworld, but maybe a bit less complex. Also check out Universim which is in a much earlier state of development and looks to go in a bit of a different direction to the other two.
Though it's not to say it can't happen, but generally most developers want to focus on a multiplayer environment rather than singleplayer, because adding the levels of "godlikeness" for the players while also having it balanced and deciding how other players interact with each other with this type of game is quite difficult to get right. A large number of people nowadays (particularly the younger generation) aren't interested in a game unless it does have multiplayer in it, judging from the responses on forums for games which are singleplayer only.
I think there is the possibility of a Stardew Valley type game set in space though, if there isn't one made already.
Mario Galaxy is technically a space game, so yea, a game set in space without Space Marines and 'Save the Galaxy' plots can be good (honestly can't remember the story of Mario Galaxy, he may actually save the galaxy, but whatever).
Really it's almost like asking "can an 'Earth' game be good without military soldiers and anti nuclear war plots?". Just take a game you like and whack it in space.
Animal Crossing, but it takes place on planet Xeta3996 and all the villagers are aliens.
Tomb Raider, but you're a Space archaeologist exploring alien ruins on the moon.
Pokemon, but you're on planet Xeta3997 catching 700+ alien creatures and making them fight each other.
Hitman, but you're on a Mars colony assassinating and dressing up as tentacle faced barmen with 12 arms.
Street Fighter, but it's in space.
It's probably why a fairly popular cliche (though not actually that commonly done) for franchises that have run out of ideas to just take that franchise and put it in space, because you can do basically the exact same thing, but it's in space.
