Listing table results in "CREATE TABLE permission denied in database" ASP.NET - MVC4

Question

I'm using ASP.NET MVC 4 - c# to connect to a live database, and list the results, however when I go to view the page it returns the following error:

CREATE TABLE permission denied in database 'DatabaseName'.

Description: An unhandled exception occurred during the execution
of the current web request. Please review the stack trace for more
information about the error and where it
originated in the code.



Exception Details: System.Data.SqlClient.SqlException: CREATE TABLE
permission denied in database 'DatabaseName'.

Source Error: 


Line 16:         public ActionResult Index()
Line 17:         {
Line 18:             return View(db.AccControls.ToList());
Line 19:         }
Line 20

Controller:

namespace TestDBCon.Controllers
{
    public class HomeController : Controller
    {
        private DataDbContext db = new DataDbContext();

        public ActionResult Index()
        {
            return View(db.AccControls.ToList());
        }

    }
}

AccControl.cs (model)

namespace TestDBCon.Models
{
    public class AccControl
    {
        public int ID { get; set; }
        public int ControlCode { get; set; }
        public string Nominal { get; set; }
        public string CostCentre { get; set; }
        public string Department { get; set; }
    }

    public class DataDbContext : DbContext
    {
        public DbSet<AccControl> AccControls { get; set; }
    }
}

Web.Config:

<add name="DataDbContext" connectionString="Data Source=***;initial catalog=***;integrated security=True;" providerName="System.Data.SqlClient" />

I'm not trying to create a table? I'm just trying to list results so I'm extremely confused. It must be something to do with the MVC?

Any help would be greatly appreciated!

Thanks

Is your web config pointing to the correct database?, can you show us the declaration and initialisation of db? — CR41G14, Feb 06 '13 at 11:03
can you make sure the user you are using have the permissions to create database. — MMK, Feb 06 '13 at 11:07
Look at the server in Management Studio, logged in as the same user as your application is connecting with, try to Create a new database. Do you get the same error? — Arran, Feb 06 '13 at 11:22
@Arran I can't create new databases / tables. But I don't need to, I only need to read from them — , Feb 06 '13 at 11:43

ghiscoding · Answer 1 · 2020-06-12T13:56:06.077

I know it's old but since I had the same problems and it took me a while to find the solution... I decided to share the info. So I had to do 2 things to get rid of this problem, 1st was disabling the migrations:

# Migrations/Configuration.cs
internal sealed class Configuration : DbMigrationsConfiguration<IntranetApplication.Models.MyDb1>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }
}

however that wasn't enough, I also had to make sure the Seeder doesn't run. You can cancel it with this extra piece of code:

#Global.asax.cs
protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    Database.SetInitializer<Models.MyDb1>(null);
    Database.SetInitializer<Models.MyDb2>(null);

    ...
}

Then finally I can now do a SELECT with LINQ and only have READ access

EDIT
As per Lawrence's suggestion, it's most probably better having it directly inside DB Context Constructor. Thanks for the tip, I updated my code and it now looks like this:

public partial class MyDb1 : DbContext
{
    public MyDb1()
        : base("name=MyDb1Connection")
    {
        Database.SetInitializer<Models.MyDb1>(null);
    }

    ...
}

This is crucial to Entity Framework for read only and should have been marked as answer. Without this you cannot change the timeout without EF trying to migrate. It however does not need to be done in the Application_Start. if can be done in the constructor of the modelContext. — Lawrence Thurman, Apr 30 '14 at 16:23
Thanks, this saved my bacon. This should be marked as the correct answer. — camainc, Jan 04 '17 at 21:39

CR41G14 · Accepted Answer · 2013-02-06T12:20:46.747

4

Is your web config pointing to the correct database?

Are the credentials correct?

Entity Framework will create tables in the database if you are going to use the MVC4 WebSecutiy() to handle the Authentication and Authorisation of users. This could be throwing the exception.

In this case where you cannot create the tables needed for the membership provider, you will need to exclude this from the system. See this here. Alternatively create a new MVC4 project and select empty template and just put in the bits you need.

edited Feb 06 '13 at 12:20

answered Feb 06 '13 at 11:06

CR41G14

5,464
5
43
64

1

The credentials are all correct, I just don't understand why MVC 4 decides to try and create an already existing table? (sorry I'm new to this) – Feb 06 '13 at 11:10
Is your DbContext using DataDbContext connection string? MVC4 will have a default connection string also. Change this to be the same as your DataDbContext connection string. Like i said MVC4 will try and create Some Users tables if you are using the built in WebSecurity() to handle the users. – CR41G14 Feb 06 '13 at 11:14
I've changed the default connection to the same as DataDbContext, which doesn't seem to change anything. I'm guessing built in WebSecurity() is enabled, is there anyway to prevent MVC4 from doing this (creating users tables) ? – Feb 06 '13 at 11:25
Empty template? SimpleMembership? Remove the Initialise attribute from the Account controller? – CR41G14 Feb 06 '13 at 12:21
I managed to get it working using the ADO.NET Entity Data Model. Thanks for all your help anyway guys! Really appreciated it. – Feb 06 '13 at 14:01

score 0 · Answer 3 · answered Jun 14 '19 at 16:49

I encountered a similar error after refactoring and renaming some code. The issue was that my C# code didn't match table name in the Database.

My code in c#:

public virtual DbSet<MyTableName>

And the table name in SQL Server:

MyActualTableName

Since the table names didn't match (MyTableName != MyActualTableName), I got this (misleading) error:

Create TABLE permission denied in database MyDatabase

Took me awhile to see what I'd messed up, but matching the table names resolved the error.

Listing table results in "CREATE TABLE permission denied in database" ASP.NET - MVC4

3 Answers3

Linked