I've been using Vista pretty much exclusively on my main desktop system at home since Beta 2. I still have RC2 running as I type this. While I agree that the Security popups are a bit of a pain, all I can tell you is that in my experience, early setup of the system is the most annoying, because that is when you are performing the most activities that will trigger the popups. Once initial setup of the OS, and applications is taken care of, the popups become much less intrusive, and the average user could probably get to a point where they virtually never see them outside of software installation - and then only for certain titles. I suspect as more software developers begin to write specifically for Vista that this may happen even less. Overall I've been very happy with the OS, and can't think of anything from XP that I "want back".
There are still some driver availability issues, so I would not recommend rushing out and buying it unless you know all your hardware is supported. If you use software that depends on OpenGL, I would say to definitely stay away for now.
Actually, I wouldn't really recommend anyone run out and buy it now unless you do windows desktop support for a living, as the ability to be ahead of your users on the learning curve can be a good thing.
I do suspect that Vista may push a few more users in to the Linux and OS X camps, which is certainly not a bad thing, but my overall impressions of Vista are largely positive thus far.