Sharing ASP.NET users between shared websites

I love the ASP.NET 2.0 Membership built in system. It has turned what was a boring and time consuming process into an almost trivially easy exercise. No more creating database tables and procedures and forms and all that.... but this isn't (or shouldn't) be news to anyone.

A handy, but not often talked about feature of the way Microsoft designed this system, revolves around the ApplicationId field that's in all the generated ASP.NET tables and is used so a single database can be used to store multiple websites. By default each instance is created with an application called simply "/", so all the users, roles, profiles etc are stored for that single application. But if you change the Application Name for any of the providers (Roles, Membership etc) in the web.config file then all connections will be made to the same database, but for a different website. 

This alone is really cool, many hosts charge per database but using this feature it would be possible to host as many websites as you like from a single database instance. Ok you'd still need to store the site specific tables, but a simple prefix would allow you to group the tables and not accidentally interfere with another site.

The company I work for is in the middle of migrating its 5 main websites from ColdFusion to .Net and though the Membership structure is very flexible, in that if you want to extend the profile and store FirstName as part of their profile then all you need to do is add a node to the web.config and the Profile table will be able to store FirstNames. However a significant problem arises with this approach if you need to query against what's stored. For example if you want to return only users whose name begins with the letter 'A' you can't simply do a WHERE FirstName LIKE '%A' because there is no FirstName column, so we're keeping our existing backend Oracle database, but adding the ASP.NET tables and linking the users by their exisitng UserID's so we can use all the built in goodness, but delve deeper into their records when we need something more sophisticated than the basic Profile.

Because the 5 websites share the same users, or a user for one site may have access permissions on one of the other sites (depending on what course they are studying, whether they've taken out a certain type of membership etc) we need to either have 5 databases that synchronise their membership tables or have a global set of tables for membership that the 5 websites use. This latter option is how we intend to go, it's less maintenance work, more scalable and means we can have Roles that span the websites simplifying our existing structure to closer resemble the real-world membership structure we have. Because of the providers in the asp.net membership system we can easily point each website at the common users database whilst maintaining separate databases for each of the other websites.