Identity tables are created in the [dbo] schema by default, when using SQL Server. We can create them in a different schema if we want to, in a few simple steps.
(This does not migrate existing data, but rather, it creates new tables in the specified schema.)
The six steps required are given below.
BEFORE STARTING ... MOST IMPORTANT:
Make a complete backup of your database before doing this! You must ensure you can recover everything from a backup if things don't work out as expected.
1. Set the connection string in appsettings.json. For example:
"DefaultConnection": "Server=YOUR-SERVERNAME;Database=YOUR-DBNAME;Trusted_Connection=True;MultipleActiveResultSets=true"
}
You can specify a local or network server, or it could be an Azure SQL database.
2. Under the Properties folder in your web project:
Open the files serviceDependencies.json and serviceDependencies.local.json, and comment out the code in both of them:
// "dependencies": {
// "mssql1": {
// "type": "mssql",
// "connectionId": "ConnectionStrings:DefaultConnection"
// }
// }
//}
3. In Program.cs, create 2 new classes ApplicationUser and ApplicationDbContext, placing them at the end of the file:
(The new classes derive from IdentityUser and IdentityDbContext, respectively.)
// https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-6.0
/// <summary>
/// Extra fields can be added to the user table here
/// </summary>
public class ApplicationUser : IdentityUser
{
//public string CustomTag { get; set; }
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
/// <summary>
/// Create ident tables in a different schema
/// </summary>
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.HasDefaultSchema("YOURCUSTOMSCHEMA");
}
}
#endregion
Your new schema will be whatever you specify as YOURCUSTOMSCHEMA at the end of the code above.
4. Also in Program.cs, change references from IdentityUser and IdentityDbContext to ApplicationUser and ApplicationDbContext respectively - for example:
options.UseSqlServer(connectionString));
builder.Services.AddDatabaseDeveloperPageExceptionFilter();
builder.Services.AddDefaultIdentity<ApplicationUser>(options => options.SignIn.RequireConfirmedAccount = true)
.AddEntityFrameworkStores<ApplicationDbContext>();
5. In Views\Shared\_LoginPartial.cshtml, change references from IdentityUser to ApplicationUser:
6. Create and run migrations, to generate the new schema and identity tables: