Associate users and roles added to ASPNET SQL Database tables with existing user information

Question

I have added a series of ASPNET database tables for roles, user and membership management to my existing SQL database using aspnet_regsql.exe.

There is already a user table in the existing database which contains information (ID, Name, Address, Postcode, etc) for a number of users. What I want to achieve to associate the new aspnet_Users table with the existing user table.

Is there any option or options for recommendation please? Thanks

Cheers, Alex

Answer 1

The UserKey, called UserId in the ASPnet membership tables, is the GUID which identifies a user. You can add a UserKey column to your Users table and then start doing dangerous things like:

select *
  from Users as U inner join
    aspnet_Users as aU on aU.UserId = U.UserKey inner join
    aspnet_Membership as aM on aM.UserId = aU.UserId
  where U.UserId = @UserId

No warranty, expressed or implied, is provided by Microsoft (or me) if you want to fiddle about directly in their tables.

Answer 2

We had a similar situation on a project I worked on a couple years ago. What we ended up doing was storing the primary key of the related user record from the external user table as a Profile Property of the ASPNET Membership model.

The benefit was that we didn't have to change anything about the schema of the external database to create the relationship and we could use the built in ASPNET Membership profile objects to easily obtain the related key from within the web code-behinds.

The initial population of this profile property was accomplished via a utility we wrote specifically for the task using ASPNET Membership Profile objects and was made easier by the fact that both our Membership setup and external table stored the email address of the user making it the key for the one time task.

The downside of this approach is that the ASPNET Membership Profile table is very much NOT denormalized (or realy normalized for that matter). It stores the Profile Properties as either xml data or serialized binary. In older versions it was serialized with the property names stored as names and character position of a single value string containing all values. This makes it hard (if not impracticle) to write queries, joins, etc from the aspect of your external table.

For us this wasn't a big deal because we were only working with the external user data on a case by case basis from the website. So, grabbing the key from the ASPNET profile using built objects and then looking it up in the external database was easy.

If your project is going to do a lot of relational queries or batch processes then I would probably recommend instead storing the ASPNET UserId GUID as a foriegn key in your external user table or if emails are going to be unique using those.