Change EF Core Migrations to different default owner

asp.net-core entity-framework-core sql-server

Question

I am creating a new .NET Core 3.1.3 application, connecting to a new SQL Server 2016 database. The connection is a trusted connection. The EF migration tool is creating the tables with me as the owner.

dotnet ef migrations add InitialDb
dotnet ef database update

I don't want that. I want the tables to be owned by a service id that we have created for the purpose. What is the safest and most efficient way to default table creation to that owner?

1
2
4/18/2020 6:09:17 PM

Popular Answer

If you don't specify a schema for your Entities, EF will generate the DDL with no schema specified. And in SQL Server this means that the object will be created in the user's default schema. Normally the user's default schema should be dbo, but if it's not, then the object will be created in a different schema.

And in SQL Server objects are owned (again by default) by the owner of the schema in which they are created.

So, configure the tables to be created in the dbo schema, or configure your user's default schema to be dbo, and the objects will be created in the dbo schema and owned by dbo.

eg in OnModelCreating, add

foreach (var et in modelBuilder.Model.GetEntityTypes())
{
    et.SetSchema("dbo");
}

or in SQL

alter user [yourdomain\youruser] with default_schema=dbo

Note that if you are the database owner, or a sysadmin, you always connect to the database as dbo, and your unqualified DDL will create objects in the dbo schema.

I want the tables to be owned by a service id

You don't normally want objects owned by the identity that the application uses to connect, as that identity should be in a role with least-privileges. EG no ability to drop tables or create triggers. Typically the objects should simply be owned by dbo.

1
4/18/2020 9:45:53 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow