Hi, I have an ASP.NET project where I want to keep the membership (SQL Provider) in a separate database and the Roles/Profiles will be per application.
Question
What is the KEY that relates between the Membership database and the Roles/Profile database? Is it the UserID or UserName?
I opened up the tables in separate expolrer and notice the UserID is different in the Membership database from that in the application Roles database.
The best thing you can do in this case is implementing your own (custom) MemberShip and Role provider. The relationship between a membership and roles are defined by yourself and the username is commonly used for this.
Remark on Poet's responses:
Perhaps i shouldn't have mentioned "best solution", but in my opinion the default AspNet membership and Role provider accompanies the Aspnet tables which are created using the aspnet -regsql command. If Microsoft's Membership and Role Provider don't fullfil your needs, you should create your own. If you create your own membership and role provider, it will be clear to other developers that they are dealing with a provider implementation which works or is structured in a different way.
My conclusion is that my solution was perhaps not "the best solution", but more a recommendation. The other thing is that the ASP.NET Providers aren't an example of good software design anyway. We are still using it, because of their compatibility with other controls. Your solution is ok, but mine will do as well and it's up to mr. Saif to choose a solution that fits best in his application.
As Microsoft mentions:
There are two primary reasons for creating a custom membership provider.
•You need to store membership information in a data source that is not supported by the membership providers included with the .NET Framework, such as a FoxPro database, an Oracle database, or other data source.
•You need to manage membership information using a
database schema that is different from the database schema used by the providers that ship with the .NET Framework
If I read your question correctly, you wish to store membership in one database instance and roles in another.
This is acceptable and possible by simply providing different connection strings. You no not need to implement a custom provider.
Understanding the fact that roles and membership are truly separate concerns, except for minor bleeding posed by the MembershipProvider.DeleteUser method, and can operate in isolation.
There is no true 'relation' between the roles and membership tables and any that are inferred by the appearance of records in the aspnet_users table are coincidental. If a record is present when a role query is made for a user, that userId is used otherwise a new record with a new userId is created.
The common value used throughout the provider stack is the username field, which, while unique to an application, is not a key.
So, as long as you are aware that you must manually perform deletion of role records when you call Membership.DeleteUser, you may simply use two databases, no custom implementation required.
Good luck