Different user for migrations
Clash Royale CLAN TAG#URR8PPP
Different user for migrations
I'm trying to run
dotnet ef database update
When I do so, I get an error about not being allowed to CREATE TABLE. Not entirely surprising as I don't want the user I have the website running under to be able to create tables. So, after a bit of searching I found a solution that basically created an inherited context, and with that context used a different set of credentials. So, I tried;
dotnet ef database update --context ScaffoldContext
And I got the same error. I checked my connection string, yes, it's a user I can use to create a table with. Confirmed through SQL CLI. So, I added CREATE TABLE privileges to my site user, and the error changed. Suggesting that the base connection string was the one that mattered and it's ignoring using my elevated user. I tried moving the configuration into the OnConfiguring override in my inherited scaffold context, instead of services.AddDbContext in my Startup.cs. However, looking this up it looks like the wrong way to go about that. When I added CREATE TABLE privilege to my site user, I got a different exception about not being allowed to touch dbo.
This is driving me nuts, I don't want to use my site user as my migration user and it seems every example I find is from older versions of EF or dotnet core. Does anybody have any solid guides on how to go about managing users correctly using migrations with 2.1?
Note: If I change my connection string to be my sa user, it works fine. So the migration will go through. I'm just not wanting to give either full privileges to my site user or swapping credentials around in connection strings every time I need to run a migration.
Because I wasn't aware that was the proper way to go and had spent insufficient time learning before diving in. I'm don't often use migrations, so rarely touch them. The changes to the dotnet core / ef core libraries mean there's documentation that's inaccurate and deprecated. It took me all of 5 minutes to get up and running once I was aware of the DTF.
– Hammerstein
Aug 12 at 23:58
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Why don't you use design-time factory to configure the db context with elevated user used just to run migrations?
– Ivan Stoev
Aug 12 at 20:08