(Relatively) stable research careers other than tenure track?
I'm an Epidemiologist, so I'll answer things from that perspective:


  "How do jobs at "research institutes" work? Or jobs at major hospitals
  that have research programs?"


Assuming it's not, as mentioned a 100% soft-money faculty position, you're essentially working as part of a larger research group. This does mean that you get to spend lots of time doing research, but it also means you're still both beholden and dependent on grant money. It's just someone else's job to get it. So you have as much job security as said grant money can provide, which can range from a huge amount to almost none at all. This is also true for hospitals - you're essentially a staff scientist, but that money needs to come from somewhere, and that somewhere is grants.

It might not be as stressful if you don't have to get them yourself, but there's also no promise of any sort of academic freedom. There may not be the time and resources to do any of that.


  Are there any positions where you have relatively good job security
  because you are there to help/consult/collaborate on multiple other
  peoples' projects, but still get a chance to have a little research of
  your own too?


This is a very hard path to climb. I'm a modeler, so much of my work is essentially doing this - shoring up my own research agenda while spending a lot of time as "Specific Aim 3" on someone's grant. I'd suggest that doesn't actually have a lot of job security behind it because it means there's lots of points of failure and a perpetual need to keep things going so that you're 100% covered. There are research organizations which hire epidemiologists, and where you're essentially paid through contracts with other companies, which may be what you're looking for, but again, the chance to have a "little research on your own" might not exist. You're working on someone else's dime, so your own passion projects are essentially an indulgence on their part.

If you're genuinely interested in research without grantwriting and a stable career, I'd suggest looking at research positions in the federal government. What agencies will depend on what aspect of epidemiology you work in.