How to use a different connection string (but same database) for migrations
entity-framework
Solution
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'.
Problem
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?