Salt Lake City definately isn't DC/NYC but it isn't podunk-Idaho/Provo either, there is a fair amount of stuff going on, especially downtown in the summer.
I just moved back last summer, I grew up in SLC but moved to the East coast for high school and college and worked a couple years abroad before I got a job offer and moved back. It's a lot different than I remember it being 5-10years ago (my parents' kept their house here so I came back every year to ski and stuff), it has grown a lot. My gf moved out here from Cincinnati last year with me and she absolutely loves it and is trying to get her friends to move out here too (I think one of them is once she graduates).
That's a trend I've noticed; people who grew up in other cities tend to love it, those of us who grew up here and went off to college are kind of iffy on being back and can't wait to move again, and there are those who never leave.
Depending on where you work and live it's really not that different from anywhere else. I work at the U of U where most aren't Mormons, and downtown is fairly low on Mormons too (I don't think anyone in my apartment building is, they all drink/smoke). Like it's been mentioned it's only if you live in the suburbs or once you get outside of the Salt Lake valley that things get "strange". Like Provo, most of the stores and restraraunts are closed on Sundays whereas in SLC everything is open.
My parents' house is in Sugarhouse so I was living there for a while but now I live downtown and it's a huge difference, I've never been a huge fan of suburbia (got used to living in the city and walking to the corner store 24/7) so it's been a good change. I actually live just a couple blocks from Trolley Square, that was a fun night with helicopters flying over every 5min. I have about 6 bars/clubs within 15min walking distance of me, not too bad, but no bar district. Last call at 1am which is sort of annoying, but bars are open on Sundays (but not the liquor stores).
Southern Utah/Moab is about 4hrs away, great ATV/hiking/outdoor stuff. Las Vegas is a 6hr drive (I go pretty much every other month). 4 major ski resorts with 30min (the high parts of Snowbird are still open until this weekend) , Park City with Deer Valley and the Canyons is like 45min. Evanston, WY is about 80miles away when you need to make a keg/liquor run