We have a .net core app being deployed to a Kubernetes cluster which accesses an AWS RDS MS SQL database.
In this environment we'd like to use EF Code First to handle our model (maybe with migrations later, but initially dropping and creating is fine).
How in this environment do we create a SQL user with appropriate permissions on the RDS instance so that the web application can login as this user and create the code first model?
Our initial approach involved creating a user as part of a .sh script, creating a db and assigning permissions. This fell down as when in the C# code we tried to run Database.EnsureExists() is saw there was a database and didn't build the model.
I thought perhaps not creating the db and assigning higher permissions to the user might work, but this feels like a bad approach unless we run some kind of post deploy to remove the dboesqe permissions afterwards.
What is the recommended approach for a ephemeral deployment where we intend to drop/create/seed on each run?
I've been tussling with this question as well. We're doing .NET Core EF code first on Kubernetes with a Microsoft SQL database.
I've been messing around with context.Database.Migrate(). This will create the DB then create the tables and do the migrations (case 1), or if the DB already exists, it will just create the tables and do the migrations (case 2).
For case 1, the account needs to have the dbcreator server role. Once it creates the DB, it will assign itself the dbo database role.
For case 2, you could potentially just give db_ddladmin, db_datareader, and db_datawriter. I've tested this and it seems to work fine, but I'm unsure of the side effects of not having dbo access. Julie?