The ASP.NET membership provider by default stores the user info in an Access file in an the \App_Data\ folder, however, you can modify this to use SQL which you should do if you're going to have a significant amount of users.
The built in functionality can save a lot of time - e.g. you can use the built in password recovery dialogs, etc. To be honest, though, I prefer to write my own user functionality from scratch as it is more transparent.