I have been doing some work with custom membership providers and found this screen cast very informative.
Membership providers are a very powerful and useful feature of .NET 2.0. The membership provider that comes out of the box is very limiting as you are restricted to using the database schema that comes with the out of the box SQL Express built adaptor. Thats all very well if you want to build your membership provider around this schema, but most people already have their own user management schema's. The screen cast only touches on what needs to be done for a simple custom provider but it's easy enough to pick up and expand on to use your own database and schema as a provider.
If you had used the membership providers in the early beta's of 2005 you may have used MS Access providers which have since been removed from 2005, however these are still available for download. It should be noted that MS Access should only be used for sample applications or small applications with few users, where possible SQL Server Express should be used for these types of applications (hobby applications) where a full version of MS SQL Server is not available. If you have written hobby applications in MS Access which you'd like to port over to SQL Server Express it is pretty easy to do this with an Export command from MS Access using the ODBC connector to SQL Server Express.