How to use a different connection string (but same database) for migrations

Question

I have been using a connection string like this to connect my website to my database:

<add name="MyDb" 
     connectionString="Data Source=MyDb;
     Initial Catalog=Staging;
     User Id=website_staging;
     Password=secret;" 
     providerName="System.Data.SqlClient" />

The website_staging user is a member of the db_ddladmin role so that when I publish, and my MigrateDatabaseToLatestVersion initialiser runs, it has the permissions required to migrate the database to the latest version automatically.

I want to reduce the standard user's permissions (by adding it only to the db_datareader and db_datawriter roles) and connect with a different user (in the db_ddladmin role) during migrations.

So I added another connection string with a different name and user:

<add name="Migrations" 
     connectionString="Data Source=MyDb;
     Initial Catalog=Staging;
     User Id=website_staging_migrations;
     Password=secret;" 
     providerName="System.Data.SqlClient" />

And I changed the name of the connection string used by the context initialised in my DatabaseMigrationConfig class:

public class DatabaseMigrationConfig
{
    internal static void Register()
    {
        using (var context = new MyDbContext(Name="Migrations"))
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, 
                                    Migrations.Configuration>());
            context.Database.Initialize(false);
        }
    }
}

And in the constructor of my Migrations.Configuration class I also changed the connection string:

internal sealed class Configuration : DbMigrationsConfiguration<SID2013Context>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;
        string cs = ConfigurationManager.ConnectionStrings["Migrations"].ConnectionString;
        TargetDatabase = new DbConnectionInfo(cs, "System.Data.SqlClient");
    }
}

I then tried publishing the website. It appears to correctly pick the connection string with the higher rights, but it attempts to run the Initial migration. How can I stop it doing that?

Answer 1

I finally solved this by adding the website_staging_migrations user to the db_datareader and db_datawriter roles as well as the db_ddladmin role.

The user obviously needs to read the data in the __MigrationHistory table. What threw me was that EF doesn't report the failure to access the table as an error and instead I got this error when it tried to run the initial migration:

There is already an object named '----' in the database.

And the user also needs to write to the __MigrationHistory table. I got this error until I added the user to the db_datawriter role:

The INSERT permission was denied on the object '__MigrationHistory',
database 'Staging', schema 'dbo'.