Sorry, I don't have Vista here. But in XP it's checkbox in the Advanced tab of the Energy management applet in Control Panel: ask password when computer comes out of standby.
Might be the same in Vista.
I am the administrator on my home computer and there aren't any other users. My problem is that there isn't a password prompt when "waking up" my computer, but it's there when I turn it on after completely shutting down. I just got this computer a week ago and this problem only started yesterday. I'm just getting familiar with it and haven't installed any programs yet (came preloaded with Vista from Dell).