Can't create EF Core migrations table; how to correctly initialize SQL Server schema, database-CodePudding

I am trying to run the very first migration using EF Core but I am getting an error:

Failed executing DbCommand (42ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'') IS NULL EXEC(N'CREATE SCHEMA [];');
CREATE TABLE [***].[Migrations] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK_Migrations] PRIMARY KEY ([MigrationId])
);

But when I connect to the database as sa and run

GRANT CREATE TABLE TO ***

I get the error

Cannot find the user '***', because it does not exist or you do not have permission

I suppose the way I created schema, database and user/login isn't correct. The database has been initialized by running these two scripts:

USE master

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 name FROM sys.databases WHERE name = N'$(Name)')
BEGIN
    CREATE DATABASE $(Name)
END

----------

USE $(Name)

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

I am using go-sqlcmd to run the scripts and for some reason I have to split the commands in two scripts, otherwise not all the objects are created. Both scripts execute without errors and I can see the database, the user and connect to the database using the created login and password but EF can't create the migrations table.

UPDATE: now the schema is correctly created in the application database.

Run:

USE {Database}

GRANT CREATE TABLE TO {User}
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [{Schema}] TO {User}

Returns error:

The specified schema name "{Schema}" either does not exist or you do not have permission to use it.

But I can access the database via Azure Data Studio using the created user and I see the new created schema.

CodePudding user response：

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 its job, I don't know if making it database owner is a good practice but for the moment it fixes the error.