The vast majority of dirt roads are fine. I put hundreds of miles on my 1996 Honda Civic hatchback in the Cascades with no problems many years ago.

If the road existed in the 1990s, it's quite likely accessible by a mid-size SUV. Similarly, if families of 4 could go camping with cars from 1950-2000, you can today as well. In fact, you can get more compact tents, etc. today.

Trucks and huge SUVs come in handy if you want to bring lots of modern toys like gigantic prestige coolers and 4x4s.

I have multiple times driven a Mustang Mach E (a heavy, very low ride height EV) out to a friend’s property an hour off of anything resembling a road, down multiple dirt switchbacks into the bottom of the Yuba River canyon.

Vehicles these days are shockingly capable.