Cant run ‘dotnet ef database update’ because of insufficient privalleges

I am at a confusing standstill with developing my .net application and setting up the database.

I am using a docker container image of mssql/ sql-express. ive got it running and can log into the sys admin account.

I created a login ‘CommanderApi’, database ‘CommanderDb’ and created a user of that db with the ‘CommanderApi’ login. then I created an initial migration, and the update fails when i try to run it.

is there something wrong with my approach? It seems that the design idea behind using a framework and entity is it takes a lot of the boilerplate sql code out of the programmers hands. However, the tutorial video im watching, they use SSMS and do all the steps I described above- minus one step- giving the user credentials which my .net applications’ connection string logs into the sufficient permissions.

when i try to manually give the CommanderApi user permissions to edit the database, i keep getting syntax errors:

Changed database context to 'CommanderDb'.
1> grant all on CommanderDb to 'CommanderApi'
2> go
Msg 102, Level 15, State 1, Server f69fca1fbe0b, Line 1
Incorrect syntax near 'CommanderApi'.
1> grant all on CommanderDb to 'CommanderApi'@'localhost'
2> go
Msg 102, Level 15, State 1, Server f69fca1fbe0b, Line 1
Incorrect syntax near 'CommanderApi'.
1> grant all on CommanderDb to CommanderApi
2> go
Msg 15151, Level 16, State 1, Server f69fca1fbe0b, Line 1
Cannot find the object 'CommanderDb', because it does not exist or you do not have permission.
1> exit
[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P super_DUPER_password
1> grant all on CommanderDb to 'CommanderApi'@'localhost'
2> go
Msg 102, Level 15, State 1, Server f69fca1fbe0b, Line 1
Incorrect syntax near 'CommanderApi'.
1> grant all on CommanderDb to [email protected]
2> go
Msg 15151, Level 16, State 1, Server f69fca1fbe0b, Line 1
Cannot find the object 'CommanderDb', because it does not exist or you do not have permission.
1> delete CommanderDb
2> go
Msg 208, Level 16, State 1, Server f69fca1fbe0b, Line 1
Invalid object name 'CommanderDb'.
1> drop CommanderDb
2> go
Msg 343, Level 15, State 1, Server f69fca1fbe0b, Line 1
Unknown object type 'CommanderDb' used in a CREATE, DROP, or ALTER statement.
1> drop 'CommanderDb'
2> go
Msg 102, Level 15, State 1, Server f69fca1fbe0b, Line 1
Incorrect syntax near 'CommanderDb'.
1> drop database if exists 'CommanderDb'
2> go
Msg 102, Level 15, State 1, Server f69fca1fbe0b, Line 1
Incorrect syntax near 'CommanderDb'.
1> drop database if exists CommanderDb
2> go
1> exit

Error I get when trying to run dotnet ef database update

C:UsersBenVsCode.netangular_templatecommander>dotnet ef database update
Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [CommanderDb];
Failed executing DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [CommanderDb];
Microsoft.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)      
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)   
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Create()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)       
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:d2819228-4d75-47ee-92f4-da88caa6a18f
Error Number:262,State:1,Class:14
CREATE DATABASE permission denied in database 'master'.

C:UsersBenVsCode.netangular_templatecommander>docker exec -it sql-server-db "bash"
[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U CommanderApi -P Commander9
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot open user default database. Login failed..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'CommanderApi'..
[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P super_Duper_password
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'SA'..
[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P super_Duper_password
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
[email protected]:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P super_DUPER_password

Does anybody know what i am doing wrong? appreciate any help.

my docker-compose.yml

version: "3.7"
services:
  sql-server-db:
    container_name: sql-server-db
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "super_DUPER_password"
      ACCEPT_EULA: "Y"

probably evident but this is a non production mode application, so none of the credentials are valid/ secure

startup.cs section were we add the db context with D/I

services.AddDbContext<CommanderContext>(options => options.UseSqlServer
                (Configuration.GetConnectionString("DefaultConnection")));

connection string

"ConnectionStrings" : 
{   
    "DefaultConnection" : "Server=localhost,1433;Initial Catalog=CommanderDb;User ID=CommanderApi;Password=Commander9;"
}

Source: Docker Questions