我终于通过添加 website_staging_migrations 用户使用 db_datareader 和 db_datawriter 角色以及 db_ddladmin 角色。

用户显然需要读取 __ MigrationHistory 表中的数据。让我想到的是EF不会将访问表的失败报告为错误，而是在尝试运行初始迁移时出现了此错误：

 数据库中已经有一个名为 ----的对象。 
 

用户还需要写入 __ MigrationHistory 表。在将用户添加到 db_datawriter 角色之前，我收到此错误：

 对对象'__MigrationHistory'，
数据库'Staging'，模式'dbo'的INSERT权限被拒绝。 
 

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?

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'.