复制数据库后无法更新表

I make a copy of my database to another database using c#.

我使用c#将我的数据库副本复制到另一个数据库。

Everything looks ok.

一切都很好看。

However, when i try to insert a row I get this error:

但是,当我尝试插入一行时,我收到此错误:

System.Data.SqlClient.SqlException: The INSERT permission was denied on the object database schema 'dbo'.

System.Data.SqlClient.SqlException:对象数据库模式“dbo”上的INSERT权限被拒绝。

This is my c# code to copy the database:

这是我复制数据库的c#代码:

public bool CreateDataBase(string dbName)
{         
        //var connstring = "data source=.;initial catalog=InformedWorker;integrated security=True;";
        var connstring = "Server=localhost;Integrated security=SSPI;database=master";
        var localhost = "localhost";

        try
        {
            Server server = new Server(localhost);



            Microsoft.SqlServer.Management.Smo.Database sourceDatabase = server.Databases["InformedWorkerTemplate"];

            Microsoft.SqlServer.Management.Smo.Database newDatbase = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);


            var clientDBPath = ConfigurationManager.AppSettings["ClientData"];


            newDatbase.FileGroups.Add(new FileGroup(newDatbase, "PRIMARY"));
            DataFile dtPrimary = new DataFile(newDatbase.FileGroups["PRIMARY"], "PriValue", clientDBPath + "\\" + dbName + ".mdf");
            dtPrimary.Size = 77.0 * 1024.0;
            dtPrimary.GrowthType = FileGrowthType.KB;
            dtPrimary.Growth = 1.0 * 1024.0;

            newDatbase.FileGroups["PRIMARY"].Files.Add(dtPrimary);

            LogFile logFile = new LogFile(newDatbase, "Log", clientDBPath + "\\" + dbName + ".ldf");
            logFile.Size = 7.0 * 1024.0;
            logFile.GrowthType = FileGrowthType.Percent;
            logFile.Growth = 10.0;

            newDatbase.LogFiles.Add(logFile);

            newDatbase.Create();

            Transfer transfer = new Transfer(sourceDatabase);
            transfer.CopySchema = true;
            transfer.CopyData = true;
            transfer.Options.DriAllKeys = true;
            transfer.CopyAllObjects = true;
            transfer.CopyAllViews = true;
            transfer.CopyAllDatabaseTriggers = true;
            transfer.CopyAllDefaults = true;
            transfer.CopyAllFullTextCatalogs = true;
            transfer.CopyAllFullTextStopLists = true;
            transfer.CopyAllPartitionFunctions = true;
            transfer.CopyAllPartitionSchemes = true;
            transfer.CopyAllPlanGuides = true;
            transfer.CopyAllRoles = true;
            transfer.CopyAllRules = true;
            transfer.CopyAllSchemas = true;
            transfer.CopyAllSearchPropertyLists = true;
            transfer.CopyAllSequences = true;
            transfer.CopyAllSqlAssemblies = true;
            transfer.CopyAllStoredProcedures = true;
            transfer.CopyAllSynonyms = true;
            transfer.CopyAllTables = true;
            transfer.CopyAllUserDefinedAggregates = true;
            transfer.CopyAllUserDefinedDataTypes = true;
            transfer.CopyAllUserDefinedFunctions = true;
            transfer.CopyAllUserDefinedTableTypes = true;
            transfer.CopyAllUserDefinedTypes = true;
            transfer.CopyAllUsers = true;
            transfer.CopyAllViews = true;
            transfer.CopyAllXmlSchemaCollections = true;
            transfer.CopyAllObjects = true;
            transfer.Options.WithDependencies = true;
            transfer.DestinationDatabase = newDatbase.Name;
            transfer.CopySchema = true;
            transfer.CopyData = true;
            transfer.Options.DriAllKeys = true;

            StringCollection transferScript = transfer.ScriptTransfer();

            using (SqlConnection conn = new SqlConnection(connstring))
            {
                conn.Open();
                using (SqlCommand switchDatabase = new SqlCommand("USE [" + newDatbase.Name + "]", conn))
                {
                    switchDatabase.ExecuteNonQuery();
                }

                foreach (string scriptLine in transferScript)
                {
                    using (SqlCommand scriptCmd = new SqlCommand(scriptLine, conn))//, transaction))
                    {
                        int res = scriptCmd.ExecuteNonQuery();
                    }
                }
            }

            return true;
        }
        catch (Exception ex)
        {
            logging.AddError(ex.ToString());
        }
        return false;
    }

This is my server/api code to add a row (I have bound my DB to Enitity Framework so I change the database)

这是我的服务器/ api代码添加一行(我已将数据库绑定到Enitity Framework,因此我更改了数据库)

public void Exchange()
    {
        try
        {
            var query = admin.Subscriptions.Where(d => d.CompanyRef == "56f55e3e-deac-4cde-ae4b-83671363a54e").FirstOrDefault();
            if (query != null)
            {
                o.Database.Connection.Open();
                o.Database.Connection.ChangeDatabase(query.DatabaseName);
                o.Customers.Add(new Customer()
                {
                    CompanyName = "CompanyName",
                    CompanyRef = "CompanyRef",
                    CustomerRef = "CustomerRef",
                    DOE = DateTime.Now,
                    Email = "Email",
                    FName = "FName",
                    MobileNo = "MobileNo",
                    Salutation = "Salutation",
                    ServerRef = "ServerRef",
                    ServerTS = DateTime.Now,
                    SName = "SName",
                    TelephoneNo = "TelephoneNo",
                    Active = true


                });
                o.SaveChanges();
            }
        }
        catch (Exception ex)
        {
            logging.AddError(ex.ToString());
        }
    }

I copy everything from DB to DB so cannot see what else I can do?

我复制了从DB到DB的所有内容,所以看不到我还能做什么?

2 个解决方案

#1

You have to script your users and/or permissions. If you apply security at the server or instance level then you need to grant access and rights for you sql server users to the database copy that you just made.

您必须编写用户和/或权限的脚本。如果在服务器或实例级别应用安全性,则需要为sql server用户授予对刚刚创建的数据库副本的访问权限。

You are almost there.

你快到了。

Managing Users, Roles and Logins

管理用户,角色和登录

You are going to have to access user credentials in your code so be sure to take appropriate caution.

您将不得不在代码中访问用户凭据,因此请务必小心谨慎。

#2

It appears I was making this more difficult for myself than I needed to.

看来我让自己比自己需要的更难。

I agree with the other posters about this being a DBA function but I had a requirement for this to be done in managed code.

我同意其他海报关于这是一个DBA功能,但我要求在托管代码中完成此操作。

Also, thanks to Ross Bush (+1) who gave me a starter as to where to look. But, I have to say that these Users were already copied over to the new database. it was just the mapping permission to the User that was not carried over.

还有,感谢罗斯布什(+1)给了我一个关于在哪里看的先发制人。但是,我必须说这些用户已经被复制到新数据库。它只是用户的映射权限,没有被转移。

The answer was simple:

答案很简单:

var localhost = "localhost";
Server srv = new Server(localhost);
Database sourceDatabase = srv.Databases["1407245195"];
var username = @"IIS APPPOOL\DefaultAppPool";
srv.Databases["1407245195"].Roles["db_accessadmin"].AddMember(username);
srv.Databases["1407245195"].Roles["db_backupoperator"].AddMember(username);
srv.Databases["1407245195"].Roles["db_datareader"].AddMember(username);
srv.Databases["1407245195"].Roles["db_datawriter"].AddMember(username);
srv.Databases["1407245195"].Roles["db_ddladmin"].AddMember(username);
srv.Databases["1407245195"].Roles["db_owner"].AddMember(username);
srv.Databases["1407245195"].Roles["db_securityadmin"].AddMember(username);

#1