User does not have permission to alter database

Question

I am using MVC 4, Entity Framework 5, and the Code First approach.

When I make changes to my model and run the application I get an error about not being able to drop the database because it is in use.

To solve this, I've implemented my own IDatabaseInitializer to force a single connection like this:

public class ForceDropCreateDatabaseInitializer<T> : IDatabaseInitializer<T> where T : DbContext, new()
{
    public ForceDropCreateDatabaseInitializer(Action<T> seed = null)
    {
        Seed = seed ?? delegate { };
    }

    public Action<T> Seed { get; set; }

    public void InitializeDatabase(T context)
    {
        if (context.Database.Exists())
        {
            string databaseName = context.Database.Connection.Database;
            string alterStatement = "ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
            string dropStatement = "USE [master] DROP DATABASE [{0}]";

            context.Database.ExecuteSqlCommand(alterStatement, databaseName);
            context.Database.ExecuteSqlCommand(dropStatement, databaseName);
        }

        context.Database.Create();

        Seed(context);
    }
}

But now I am getting a permissions error on this line:

context.Database.ExecuteSqlCommand(alterStatement, databaseName);

The exception message:

User does not have permission to alter database '@p0', the database does not exist, or the database is not in a state that allows access checks.

ALTER DATABASE statement failed.

I presume @p0 is used because I am using parameters. How can I avoid this error and successfully drop and create my database each time I make model changes?

Answer 1

I attempted to use your question here to fix my issue with dropping the database in use. I unfortunately ran into a different issue discussed below. Here is the code I used that might fix the issue for you by completing it slightly differently:

// Somewhere in Global.asax
Database.SetInitializer(new DbInitializer());

// DbInitializer    
public class DbInitializer : IDatabaseInitializer<Context>
{
    public void InitializeDatabase(Context context)
    {
        if (context.Database.Exists())
        {
            //if (!context.Database.CompatibleWithModel(true))
            //{
                string singleUserModeCommand =
                string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
                            "databasename" /*context.Database.Connection.Database does not seem to work here so i have "fixed" the database name rather than letting it be dynamic*/);
                if (context.Database.Exists())
                {
                    context.Database.ExecuteSqlCommand(singleUserModeCommand);
                    context.Database.Delete();
                }
                context.Database.Create();
                Seed(context);
            //}

        }
        else
        {
            context.Database.Create();
            Seed(context);
        }
    }

    protected void Seed(Context context)
    {

    }
}

The biggest issue I had when using this code was that

context.Database.Connection.Database

didn't seem to want to resolve to the database name and kept giving me the error:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

So I just fixed the database name in my Context class:

public class MyContext : DbContext
{
    public MyContext()
        base: ("databasename")
    {}
}