"Cannot find the object "dbo.xxxx" because it does not exist or you do not have permissions."

Question

In my MVC Web App, I've added a model called Competencies to the existing models, and did add-migration and update-database, it worked fine, and then I've created a controller based on that model, it was OK.

But after that, I've realized that something was wrong, so I've deleted the controller and its views with the model. I also have deleted the table from Server Explorer, and have created a new model called CompetencyLabel, and run add-migration, it works fine, but when I run update-database, I got this error in PM> window:

"Cannot find the object "dbo.Competencies" because it does not exist or you do not have permissions."

That table has been deleted and I do not know why it still asks me that. Does anybody know how to make it working? thanks a lot.

Answer 1

When you get this error, try to arrange your Up() statements in order. For example,

When you look at the image above, you realize that, the Users table is renamed in the beginning and on the next line, a SQL statement is generated to drop a foreign key in the Users table but to Entity Framework, that table (that is the Users table) doesn't exist anymore so it will throw an error.

Rearrange the statements so that when the Users table is renamed, it is not called again. An example is seen in the image below.

Answer 2

When you enter Enable-Migrations command, a Migrations folder is created in the project. Whenever you Add-Migration, a new migration file is created in that folder. In the file, there are two methods defined:

• Up() - defines actions to take, when we are upgrading the database using Update-Database
• Down() - defines actions to take when we are downgrading the database using Update-Database

As you have deleted Competencies model, the latest migration added code to the Up() method, which destroys indexes on Competencies table and drops the table. As you have done it manually, just remove the code responsible for it and Update-Database. It should upgrade it without problems.

Answer 3

Even if you have redone the migrations of your project, EF will still remember schema changes.

We had a similar problem early in a project and we fixed it by removing the _MigrationHistory table from the database and then regenerated the migrations.

Answer 4

I found another answer for this problem.

My scenario was that I made changes in my entities (change relations and entitites names). When i ran the update-database -verbose I found that it was running some functions with the "EntityHistory" but that entity didnt exist anymore because I changed the name of it to "AnotherEntityHistory". Then, as result I get the "Cannot find the object "dbo.EntityHistory" because it does not exist or you do not have permissions".

What I did was rename the AnotherEntityHistory to EntityHistory again, run the Update-database (everything will be ok because it now has the "missing" entity EntityHistory) , rename again the EntityHistory to AnotherEntityHistory and run update-database again.

You will see that now it only has to rename the table instead of renaming tables, drop indexes, etc....

In this way you dont have to remove any table or the migration values.

Answer 5

As you have deleted / modified the database contents manually after applying the Add-Migration and before doing Update-Database (I guess Error is generated here)

• Delete corresponding migration file from Migration Folder as its not applied correctly revert the previous migration using Update-Database –TargetMigration: OldMigration or delete the generated db or need to clear old migration record from database table
• Apply the migration Add-Migration from NPM code first migration
• Perform the database update from Add-Migration command

This resolves your problem.

Answer 6

remove all migration classes that you have added manually in Migrations folder (by add-migration command) type this command add-migration MigrationName -ProjectName YourProjectName

if every thing works fine you should see the following code in Up method of newly created migration class

DropTable("dbo.Competencies"); CreateTable("dbo.CompetencyLabel");

then run this command update-database -verbose -ProjectName YourProjectName

happy coding

Answer 7

My problem was similar to the one described in @EduLopez' answer.

1. Much like this answer and this answer, I manually modified a migration. Specifically, I wanted to rename a table (rather than drop and create a table).

2. And my code was as such:

       protected override void Up(MigrationBuilder migrationBuilder)
       {    
           migrationBuilder.RenameTable(
               name: "oldTableName",
               schema: "oldSchemaName",
               newName: "newTableName"                    
           );
       }
   

---

Solution: Just like @EduLopez suggested, I ran update-database -verbose and discovered that I needed to specify the new schema name:

        protected override void Up(MigrationBuilder migrationBuilder)
        {    
            migrationBuilder.RenameTable(
                name: "oldTableName",
                schema: "oldSchemaName",
                newName: "newTableName",
                newSchema: "newSchemaName" // added this line
            );
        }

Answer 8

I had a similar problem. This problem arises when the user unable to create a table in the database. To solve this you need to grant access the user to create table in database.

You can add db_ddladmin or db_owner database Role membership for your database User.

Answer 9

First let me to tell reason of this error:

You delete migration manually and after that you think its enough.But in new migration that you create its effect is exist.

Solution:

Open all migration one by one and remove parts that indicate to dbo-xxxx

just it:)