Create users and roles in SQL Script with ASP.NET Identity

Question

We have used Membership earlier and now in the new project we are using Identity for authentication and authorization. We are able to create users and role programmatically (in Seed method of Identity project). We also need to link these membership user to application user e.g Employee while creating users in Seed method of application DAL. For which earlier we had used SQL Script. Now similar SQL Script we need to write to create Identity users and roles. From SQL Script we could grab MembershipId for user and use this MembershipId value to assign it Employee's MembershipId column.

The problem we are stuck at is, the stored procedures e.g. "aspnet_Membership_CreateUser" which was available in Membership DB (aspnetdb), not available in Identity DB we created.

The script looks like below:

    -- Create roles for 'XXX' Application 
    EXEC [aspnet_Roles_CreateRole] 'XXX','ADMIN'
    EXEC [aspnet_Roles_CreateRole] 'XXX','USER'

    -- Create new membership user 
    EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]
    @ApplicationName = N'XXX',
    @UserName = N'testadmin@xxx.com',
    @Password = N'QhoM803ew/sdfdf/4NQ=',
    @PasswordSalt = N'dGzG1ddfdfdfk1Kqwddff==',
    @Email = N'testadmin@xxx.com',
    @PasswordQuestion = N'secretQuestion',
    @PasswordAnswer = N'secretAnswer',
    @IsApproved = true,
    @CurrentTimeUtc = @nowUtc,
    @CreateDate = @now,
    @UniqueEmail = 1,
    @PasswordFormat = 1,
    @UserId = @MembershipId_OrgAdmin OUTPUT

    PRINT 'Test Admin Created successfully.' 

    -- Assign role to user
    EXEC  [aspnet_UsersInRoles_AddUsersToRoles]
        @ApplicationName  = N'XXX',
        @UserNames        = N'testadmin@xxx.com',
        @RoleNames       = N'ADMIN',
        @CurrentTimeUtc   =  @nowUtc


    PRINT 'Test Admin assigned to ADMIN Role successfully.' 

    -- Finally create a application user and connect it to membership user

INSERT INTO OrganizationUser
(MembershipId,IsDeleted,FirstName,LastName,PhoneNumber,Extension,Address1,Address2,City,State,ZipCode,Country,JobTitle,IsActive,OrganizationId,IsPasswordReset,DownloadCode,IsContactPerson,LastLoginDate,WelcomeEmailDate,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy)
VALUES
(@MembershipId_OrgAdmin,0,'XXX','Admin','2888262','800','ABC','Charlotte, SC 21270','SC','SC','21270','US','XXX Admin',1,(select OrganizationId from Organization where  Name='XXX'),0,'1-1-14110985',1,null,null,@now,null,@now,null)

Question:

1. Does these stored procedure exist in Identity?
2. What are the possible/suggested ways to deal with this situation i.e. link membership user with application user using Identity?

Answer 1

There are many examples for moving from membership to Identity. Take a look here. I had to do so myself for a couple of projects and ended up writing some code that takes the users from membership tables with their roles, iterating through, and creating new ApplicationUsers in the Identity tables, custom for my needs. Since you can add properties to ApplicationUser (what is tricky in Membership), you can write some code that takes what you need from membership (name, username, email etc), manipulate and creating the Users. Did have some problems with the passwords stored at the membership, as in Identity they are hashed and in membership you can choose plain text, encrypted or hashed.

As for your questions:

1. Identity have no stored procedures nor views. all queries are generated through EntityFramework.
2. Create new ApplicationUsers based on your old membership, and drop the membership (tables, views, SP, providers) after its done. I believe "linking" the membership users to identity will cause a lot of trouble along the way. All the authentication and managing stuff will be dealt by Identity and its UserManger, SigninManager, RolesManager and so on, and you dont need Membership to tangle along.