What you suggest is certainly achievable. Personally I use the default providers built in to .net as most of the effort is taken care of see here for information about these
]. The SQL providers work in conjunction with MS SQL-Server and you can even created the database using the built in aspnet_regsql.exe
That said, one size does not fit all, you can subclass the existing providers (including the base providers for a completely clean slate) to create custom ones, but I think this is only worth it if the existing ones do not meet your requirements. My advice is to try the existing ones for each tranche of functionality (auth, roles etc) until they don't do what you want and then work on your own custom provider.