如何使用不同的连接字符串（但相同的数据库）进行迁移 [英] How to use a different connection string (but same database) for migrations

问题描述

我一直在使用这样的连接字符串将我的网站连接到数据库：

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

website_staging 用户是 db_ddladmin 角色，以便在发布并运行 MigrateDatabaseToLatestVersion 初始化程序时，它具有将数据库迁移到的必需权限。我想减少标准用户的权限（通过仅将其添加到 db_datareader 并 db_datawriter 角色），并在迁移期间与其他用户（以 db_ddladmin 角色）连接。

所以我添加了另一个具有不同名称和用户的连接字符串：

 <添加名称= Migrations 
 connectionString =数据源= MyDb; 
初始目录=暂存； 
用户ID = website_staging_migrations; 
密码=秘密; 
 providerName = System.Data.SqlClient /> 
  

然后我更改了 DatabaseMigrationConfig 类：

 公共类DatabaseMigrationConfig 
 {
内部静态无效Register（）
 {
 using（var context = new MyDbContext（Name = Migrations））
 {
 Database.SetInitializer（new MigrateDatabaseToLatestVersion< MyDbContext，
迁移.Configuration>（））; 
 context.Database.Initialize（false）; 
} 
} 
} 
  

在我的构造函数中 Migrations.Configuration 类我还更改了连接字符串：

 内部密封类Configuration：DbMigrationsConfiguration< SID2013Context> 
 {
 public Configuration（）
 {
 AutomaticMigrationsEnabled = false; 
 AutomaticMigrationDataLossAllowed = false; 
字符串cs = ConfigurationManager.ConnectionStrings [ Migrations]。ConnectionString; 
 TargetDatabase = new DbConnectionInfo（cs， System.Data.SqlClient）; 
} 
} 
  

然后我尝试发布该网站。它似乎正确选择了具有较高权限的连接字符串，但是它尝试运行 Initial 迁移。

解决方案

我终于通过添加 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'.

这篇关于如何使用不同的连接字符串（但相同的数据库）进行迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！