Entity Framework Core can't create migrations table using created login

Question

I am doing a some training with .Net 6.0, C# and Entity Framework Core. I got to the point that EF can generate a new migration but hen I run dotnet ef database update I am getting an error:

Failed executing DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'{schema}') IS NULL EXEC(N'CREATE SCHEMA [{schema}];');
CREATE TABLE [{schema}].[Migrations] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK_Migrations] PRIMARY KEY ([MigrationId])
);
The specified schema name "{schema}" either does not exist or you do not have permission to use it.

Since I am working with a M1 chip laptop, I am running locally the database using azure-sql-edge docker image and initialized with go-sqlcmd using these SQL scripts (I have to split the commands in more scripts, otherwise not all objects are created):

USE master

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$(Name)')
BEGIN
    CREATE DATABASE $(Name)
END

---

USE $(Name)

IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'$(Schema)')
BEGIN
    EXEC sys.sp_executesql N'CREATE SCHEMA [$(Schema)] AUTHORIZATION [dbo]'
END

IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = '$(Login)') BEGIN
    CREATE LOGIN $(Login) 
    WITH PASSWORD = '$(Password)'
END

IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = '$(User)') BEGIN
    CREATE USER $(User) FOR LOGIN $(Login)
END

---

USE $(Name)

GRANT CREATE TABLE TO $(User);
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [$(Schema)] TO $(User);

I can connect to the database via Azure Data Studio using the created login and I see database and schema but EF can't create the migrations table with the same login.
More info:

Startup.cs

public class Startup
{
    public static void Configure(ConfigurationManager configuration, IServiceCollection services)
    {
        IConfiguration appSettings = new ConfigurationBuilder()
            .AddJsonFile($"{Environment.CurrentDirectory}/appsettings.json")
            .AddEnvironmentVariables()
            .Build();

        ConfigureInjector(configuration, appSettings, services);
        ConfigureDatabase(appSettings, services);
    }

    private static void ConfigureInjector(
        ConfigurationManager configuration,
        IConfiguration appSettings,
        IServiceCollection services
    )
    {
        services.AddSingleton<DatabaseConfig>(
            appSettings.GetRequiredSection("Database").Get<DatabaseConfig>()
        );
    }

    private static void ConfigureDatabase(IConfiguration configuration, IServiceCollection services)
    {
        DatabaseConfig databaseConfig = configuration
            .GetRequiredSection("Database")
            .Get<DatabaseConfig>();

        Console.WriteLine(databaseConfig.ConnectionString);

        services.AddDbContext<TipJarContext>(
            options => options.UseSqlServer(databaseConfig.ConnectionString)
        );
    }
}

DatabaseConfig.cs

public class DatabaseConfig
{
    public string Host { get; set; }
    public int Port { get; set; }
    public string Schema { get; set; }
    public string Name { get; set; }
    public string User { get; set; }
    public string Login { get; set; }
    public string Password { get; set; }

    public string ConnectionString
    {
        get { return $"Server={Host},{Port};Database={Name};User Id={Login};Password={Password}"; }
    }
}

Context

public class TipJarContext : DbContext
{
    private readonly DatabaseConfig databaseConfig;

    public DbSet<User> Users { get; set; }

#pragma warning disable CS8618 // Non-nullable field must contain a non-null value when exiting constructor. Consider declaring as nullable.
    public 
{ProjectName}
TipJarContext(DbContextOptions<TipJarContext> options, DatabaseConfig databaseConfig)
        : base(options)
    {
        this.databaseConfig = databaseConfig;
    }
#pragma warning restore CS8618 // Non-nullable field must contain a non-null value when exiting constructor. Consider declaring as nullable.

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(
            databaseConfig.ConnectionString,
            x => x.MigrationsHistoryTable("Migrations", databaseConfig.Schema)
        );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(databaseConfig.Schema);
    }
}

User.cs

public class User : EntityBase
{
    [Required]
    [MaxLength(255)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(255)]
    public string LastName { get; set; }

    [Required]
    [MaxLength(255)]
    public string Email { get; set; }

    [Required]
    public byte[] Password { get; set; }

    [Required]
    public byte[] Salt { get; set; }
}

EntityBase just contains audit columns.

This is the printed connection string when I try to update the database:

Server=localhost,1433;Database=TipJar;User Id=TipJarLogin;Password=***

The configuration is correctly read from appconfig.json.

Answer 1

The idea at the beginning was to give the user the least permissions possible necessary to just do it's job,

PoLP, Principle of Least Privilege! Yes, that is a very good privilege.

When I saw your original post, I did not make the connection with the failing SQL and the code that was below. So I did not realise that you were running the reduced permissions.

What was missing is ALTER permission. You need that to create a table (or other object in the schema). Here is an example:

CREATE SCHEMA extra
go
CREATE USER extrauser WITHOUT LOGIN
go
GRANT CREATE TABLE TO extrauser
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON SCHEMA::extra TO extrauser;
go
EXECUTE AS USER = 'extrauser'
go
IF SCHEMA_ID('extra') IS NULL EXEC(N'CREATE SCHEMA extra;');
CREATE TABLE extra.[Migrations] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK_Migrations] PRIMARY KEY ([MigrationId])
);
go
REVERT
go
DROP TABLE IF EXISTS extra.Migrations
DROP USER extrauser
DROP SCHEMA extra

EXECUTE AS USER permits me to impersonate the test user to test the permissions. Very, very handy.

Note that the schema needs to be created in advance. If you want the user to also be able to create schemas, you need to grant the user CREATE SCHEMA, but in that case, the user can create any schema.

Answer 2

I made it work by replacing the two GRANT statements with

ALTER ROLE [db_owner] ADD MEMBER [$(User)]

The idea at the beginning was to give the user the least permissions possible necessary to just do it's job, I don't know if making it database owner is a good practice but for the moment it fixes the error.