If you stay on the main roads, there's no need for an SUV. However, there are some very fun dirt (more like mud) roads in Ko'kee State Park that get to some great hiking areas & picnic areas, and for those you want a real off-road vehicle. We rented a Jeep Wrangler for that. If you think that sounds like fun, then consider renting an off-road vehicle.
Another fun thing to do on Kauai is river kayaking. There are also sea kayaking tours, but river kayaking is safer and calmer, for those who aren't very experienced, and the rivers are gorgeous. You can rent kayaks along the Wailua River on the east side of the island, and along the Hanalei River on the north side. Both rivers are great for kayaking, with great scenery. (One of my gallery photos here was taken from a kayak on the Hanalei River.)
Lisa