Sleepy, password protected Mac

[UniAce]

I was struggling with this for a while too (on my MacBook), then I found a solution!

A third-party app called Utility Lock:
http://semaja2.net/utilitylockinfo

I now use Utility Lock for password protection instead of the built-in OS X password protection. I use Energy Saver to schedule a wakeup a few minutes before SuperDuper's scheduled backup, and have SuperDuper put the computer back to sleep when it's done. Utility Lock keeps the computer locked from user input until the correct password is entered, but allows scheduled processes to be executed "behind" it.

The documentation is a little sparse, but it totally works. It's donation-ware, so remember to donate to the developer if you find it useful (I did).