By default, Windows 7 automatically asks you for credentials (assuming you added a password to your account), when it wakes up from sleep mode or hibernation. But you can prevent Windows from asking for a password when it resumes from standby. If you're using a laptop or tablet in a public place (coffee shop, airport, library...), remember to always lock your PC: hold down the Windows logo key on your keyboard ("WinKey"), and press L (as in Lock!)
You can prevent Windows 7 from showing the login screen when waking up through your PC's Power Options. Open the start menu and type "password". Click on the "Require a password when the computer wakes" result link.
At the bottom of that Power Options / System Settings screen in the Control Panel, you'll find settings that are disabled / grayed out (depending on your UAC settings, and on your user account type). Note: you should keep the default UAC level, and only use Windows while logged in as a standard user. This limits the damage to your machine if you get infected by a virus.
Click on "Change settings that are currently unavailable", and enter the administrator's password. Select "Don't require a password - When your computer wakes from sleep, anyone can access your data because the computer isn't locked". The fine prints? You can still lock it before you leave your PC unattended or close the laptop lid! Click Save changes to apply.
Bonus Tip: if you've enabled a screensaver, you can also turn off the logon / password prompt when the screen saver is interrupted. Open the start menu once more, and type "password". Click on the Set screen saver password link under Control Panel. When the dialog opens, uncheck the "On resume, display logon screen" checkbox, and click OK to save the change.