0

I have empty database (no tables) and I want to use Update-Database to create all tables from my models. So, I have added an initial migration which shows all tables which I have to add to database.

My connectionstring is: Data Source=webhw27.htx-net.pl,51433;Initial Catalog=1832_testowa;Integrated Security=false;User ID=1832_admin;Password=******;Encrypt=True;TrustServerCertificate=True

During Update-Database I get

CREATE DATABASE permission denied in database 'master'

Why is it trying to create database master while I want to update 1832_testowa db? I also added Database.SetInitializer<ApplicationDbContext>(null); to Global.asax which I found in another topic - still the same.

In hosting panel I set this db to user 1832_admin as default - still the same problem.

I also added this connection to Visual Studio Server Explorer and I can easly create any table in this database manualy.

Where is the problem, why I can't use Update-Database to migrate now (and in future if I'll have to add something)?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Cezar
  • 345
  • 6
  • 18
  • 1
    `master` contains the main catalog of all databases. This means that your account doesn't have permission to create new databases. This permission is given to members of the `dbcreator` server role and the administrator – Panagiotis Kanavos Sep 19 '18 at 14:33
  • 2
    Possible duplicate of [CREATE DATABASE permission denied in database 'master' (EF code-first)](https://stackoverflow.com/questions/11231934/create-database-permission-denied-in-database-master-ef-code-first) – Igor Sep 19 '18 at 14:36
  • [This migrated](https://dba.stackexchange.com/questions/78388/create-database-permission-denied-in-database-master-unable-to-get-the-permis) question explains how to add a user to the `dbcreator` role. There are duplicate questions in SO [like this one](https://stackoverflow.com/questions/11231934/create-database-permission-denied-in-database-master-ef-code-first) but the *accepted* answer is actually a bad answer – Panagiotis Kanavos Sep 19 '18 at 14:36
  • @Igor not a good duplicate as the accepted asnwer is bad. The second one is actually very good. Other answers aren't that good either – Panagiotis Kanavos Sep 19 '18 at 14:38
  • @PanagiotisKanavos - although I guess you can vote to delete it (the accepted answer that is)... – Igor Sep 19 '18 at 14:42
  • All anwers contains resolves which shows how to add privilages to account, but this is not my server, I just bought a hosting, where I can create up to 2 databases and up to 2 users (to which I can only select default database). – Cezar Sep 19 '18 at 14:43
  • Then you need to talk to your hosting provider. The account you are using lacks the privilege to create a database and you need to grant that right to that account (indirectly using a role or directly) somehow **or** use a different account that does have that privilege. – Igor Sep 19 '18 at 14:48
  • My guess is that you have to tell your framework somehow that `database` already exists and it does not need to create one. Just tables. `master` is a system database which contains e.g. `sys.databases` system view, so DB creation, let's say, _happens there_ - that's why it is mentioned here. The thing is - you don't need DB creation at all. I'm no asp/ef developer so I can't say where is that option there or even whether it exists. – Ivan Starostin Sep 19 '18 at 15:06

1 Answers1

1

Try writing a simple query to create a table in your database in SQL Server(drop or delete the table later if required), if it executes successfully, you need to check if you have mentioned the Login Name, Username and Password correctly in the Web Config file of Visual Studio.

Example:

<add name="DefaultConnection" connectionString="Data Source=DESKTOP-J87BQMD\SQLEXPRESS;Initial Catalog=cust1;User ID =xxxx;Password=xxxx" providerName="System.Data.SqlClient" />

Here, also check if the name "DefaultConnection"is the same name that you have mentioned in the xyz.cs file, where you have mentioned the sql connection as follows,

        SqlConnection con = null;

        string result = "";

        try

        {
           con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());
           //your further code
        }

If you have done all this right, try the procedure mentioned below,

In SQL server, Go to Security -> Logins -> select & double-click your login(eg:sa) ->

  1. Server Roles >> (make sure that public and sysadmin are selected)

  2. User Mapping >> (only your database must be ticked, uncheck 'master' and other databases; Database Role Membership for:(db_name) >> select(tick) db_owner and db_security admin.

  3. Status >> Permission-Grant, Login-Enabled

    I was facing the same error and the steps i have mentioned above removed that error, hope this solves yours too.

Community
  • 1
  • 1
Riya
  • 11
  • 2