CREATE DATABASE permission denied in database 'master' (EF code-first)

A

30

92

I use code-first in my project and deploy on host but I get error

CREATE DATABASE permission denied in database 'master'.

This is my connection string:

<add name="DefaultConnection" 
     connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet-test-2012615153521;Integrated Security=False" 
     providerName="System.Data.SqlClient;User ID=test;Password=test"/>

Ashok answered 27/6, 2012 at 17:34 Comment(0)

D

-13

Permission denied is a security so you need to add a "User" permission..

Right click you database(which is .mdf file) and then properties
Go to security tab
Click Continue button
Click Add button
Click Advance button
Another window will show, then you click the "Find Now" button on the right side.
On the fields below, go to the bottom most and click the "Users". Click OK.
Click the permission "Users" that you have been created, then Check the full control checkbox.

There you go. You have now permission to your database.

Note: The connection-string in the above questions is using SQL-server authentication. So, Before taking the above step, You have to login using windows-authentication first, and then you have to give permission to the user who is using sql-server authentication. Permission like "dbcreator".

if you login with SQL server authentication and trying to give permission to the user you logged in. it shows, permission denied error.

Demote answered 26/1, 2013 at 1:24 Comment(5)

How can one right-click the database, when the permission to create databases is denied? – Scantling 7/11, 2014 at 9:33

Cant't right-click on a database when is it even not created yet! – Demagogic 22/9, 2015 at 17:54

In sql server 2008 R2, step 3. should be Click Edit instead of Continue. – Busk 22/11, 2015 at 11:9

Why is this an answer if 34 users, who have no database created yet, were instructed to edit the settings of an existing database ? – Bushmaster 4/7, 2016 at 12:52

CREATE DATABASE permission denied right click you database. Wait, what? – Octavo 11/2, 2019 at 3:3

P

100

I had the same problem. This what worked for me:

Go to SQL Server Management Studio and run it as Administrator.
Choose Security -> Then Logins
Choose the usernames or whatever users that will access your database under the Logins and Double Click it.
Give them a Server Roles that will give them credentials to create database. On my case, public was already checked so I checked dbcreator and sysadmin.
Run update-database again on Package Manager Console. Database should now successfully created.

Here is an image so that you can get the bigger picture, I blurred my credentials of course:

Papillary answered 11/6, 2017 at 16:22 Comment(4)

I am getting "Add member failed for serverRole 'dbcreator'" error, when I click OK after changing server roles – Maryannemarybella 4/7, 2017 at 22:21

What should i do on sql server 2017 on windows server 2016 where there are no Builtin/Users? Working with IIS , network service for the app – Mobcap 8/4, 2018 at 10:58

@williamcage, the same problem. When your current user doesn't have dbcreator premission, you can't assign such role for another user too. So I changed the current user and solved. – Panathenaea 18/11, 2019 at 5:50

I needed to add roles to BUILTIN\Users and not to my windows user. – Differential 19/3, 2021 at 23:43

G

22

Be sure you have permission to create db.(as user2012810 mentioned.)

or

It seems that your code first use another (or default) connection string. Have you set connection name on your context class?

public class YourContext : DbContext
    {
        public YourContext() : base("name=DefaultConnection")
        {

        }

        public DbSet<aaaa> Aaaas { get; set; }
    }

Gluteus answered 5/10, 2013 at 13:43 Comment(1)

+1 for "wrong connection string" - I got this error because the database name in the connection string was misspelled. – Abmho 23/11, 2021 at 21:35

H

20

I got the same problem when trying to create a database using Code First(without database approach). The problem is that EF doesn't have enough permissions to create a database for you.

So I worked my way up using the Code First(using an existing database approach).

Steps :

Create a database in the Sql server management studio(preferably without tables).
Now back on visual studio, add a connection of the newly created database in the server explorer.
Now use the connection string of the database and add it in the app.config with a name like "Default Connection".
Now in the Context class, create a constructor for it and extend it from base class and pass the name of the connection string as a parameter. Just like,
```
public class DummyContext : DbContext
{
  public DummyContext() : base("name=DefaultConnection")
  {
  }  
}
```

5.And now run your code and see the tables getting added to the database provided.

Heeled answered 6/12, 2014 at 7:46 Comment(0)

S

12

Run Visual Studio as Administrator, it worked for me

Seda answered 14/10, 2015 at 19:51 Comment(1)

The account you're running it under should also have sysadmin or dbcreator permissions in the database. – Halflife 14/8, 2018 at 17:19

L

9

This error can also occur if you have multiple projects in the solution and the wrong one is set as the start-up project.

This matters because the connection string used by Update-Database comes from the start-up project, rather than the "Default project" selected in the package manager console.

(credits to masoud)

Lynnettelynnworth answered 3/1, 2017 at 9:23 Comment(0)

O

8

I have resolved this problem in my way. Try connection string in this way:

<add name="MFCConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MFC.mdf;Initial Catalog=MFC;Integrated Security=false;User ID=sa;Password=123"
  providerName="System.Data.SqlClient" />

remember to set default db from master to MFC (in your case, aspnet-test-2012615153521).

Overton answered 6/9, 2013 at 2:32 Comment(2)

btw, in developing environment, set "Integrated Security = true" to avoid an error "only Intergrated security can ...", or you will not be anble to visit the db via visual studio. – Overton 8/9, 2013 at 10:21

Thank you. It worked for me but with some changes. I removed "\SQLEXPRESS" from connectionString and entire ";AttachDbFilename=|DataDirectory|\MFC.mdf". – Photophilous 1/6, 2023 at 10:47

D

7

I encountered what appeared to be this error. I was running on windows and found my administrator windows user did not have administrator privileges to database.

Shut down SQL Server from ‘Services’

Open cmd window (as administrator) and run single-user mode as local admin with this command (the version of MSSQL may differ):

"C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

Open another cmd window (as administrator)
Open sqlcmd on that terminal with:

sqlcmd -S .\SQLEXPRESS

Now add the sysadmin role to your user:

sp_addsrvrolemember 'domain\user', 'sysadmin'
GO

Re-enable SQL Server from ‘Services’

Credit to: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/76fc84f9-437c-4e71-ba3d-3c9ae794a7c4/

Denverdeny answered 11/2, 2019 at 2:56 Comment(4)

Worked in my scenario, when I couldn't even grant privs in 'normal' scenario (from mssms) – Follansbee 19/4, 2019 at 13:36

It worked for me and I think it is the most consistent way which will work for many scenarios. Thanks. – Interweave 18/1, 2022 at 5:31

Hmm, "Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission." – Anemology 26/4, 2022 at 8:55

Use only . into sqlcmd command when connecting to your MSSQL instance if it's the default one (e.g. MSSQLSERVER). – Tristis 3/1, 2023 at 14:30

E

6

Double check your connection string. When it points to non-existing database, EF tries to create tables in master database, and this error can occur.
In my case there was a typo in database name.

Expectorate answered 30/3, 2018 at 7:18 Comment(1)

Thanks for the pointer. Sometimes, small things get overlooked. I replaced Db name in the web.config with the new user name. – Bakunin 29/7, 2019 at 4:28

A

4

Create the empty database manually.
Change the "Integrated Security" in connection string from "true" to "false".
Be sure your user is sysadmin in your new database

Now I hope you can execute update-database successfully.

Acrosstheboard answered 15/1, 2020 at 15:22 Comment(0)

S

3

As the error suggests, the SQL login has no permission to create database. Permissions are granted when the login have the required roles. The role having permission to create, alter and drop database is dbCreator. Therefore it should be added to the login to solve the problem. It can be done on SQL Management Studio by right-clicking the user then go to Properties>Server Roles. I encountered the same error and resolved it by doing exactly that.

Similitude answered 5/1, 2017 at 3:55 Comment(0)

C

3

If you're running the site under IIS, you may need to set the Application Pool's Identity to an administrator.

Cutlor answered 17/1, 2017 at 10:25 Comment(0)

B

2

run this on your master database

ALTER SERVER ROLE sysadmin ADD MEMBER your-user;  
GO

Barbet answered 12/11, 2020 at 19:1 Comment(2)

The ALTER SERVER ROLE command documentation is available at: learn.microsoft.com/en-us/sql/t-sql/statements/… – Tristis 3/1, 2023 at 14:35

You may need to create the user if it's a local/domain Windows account with the CREATE LOGIN command before running the command above (e.g. CREATE LOGIN [EXAMPLE\Administrator] FROM WINDOWS;). Please see learn.microsoft.com/en-us/sql/database-engine/configure-windows/… for details. – Tristis 3/1, 2023 at 14:38

C

1

I'm going to add what I've had to do, as it is an amalgamation of the above. I'm using Code First, tried using 'create-database' but got the error in the title. Closed and re-opened (as Admin this time) - command not recognised but 'update-database' was so used that. Same error.

Here are the steps I took to resolve it:

1) Opened SQL Server Management Studio and created a database "Videos"

2) Opened Server Explorer in VS2013 (under 'View') and connected to the database.

3) Right clicked on the connection -> properties, and grabbed the connection string.

4) In the web.config I added the connection string

   <connectionStrings>
<add name="DefaultConnection"
  connectionString="Data Source=MyMachine;Initial Catalog=Videos;Integrated Security=True" providerName="System.Data.SqlClient"
  />
  </connectionStrings>

5) Where I set up the context, I need to reference DefaultConnection:

using System.Data.Entity;

namespace Videos.Models
{
public class VideoDb : DbContext
{
    public VideoDb()
        : base("name=DefaultConnection")
    {

    }

    public DbSet<Video> Videos { get; set; }
}
}

6) In Package Manager console run 'update-database' to create the table(s).

Remember you can use Seed() to insert values when creating, in Configuration.cs:

        protected override void Seed(Videos.Models.VideoDb context)
        {
        context.Videos.AddOrUpdate(v => v.Title,
            new Video() { Title = "MyTitle1", Length = 150 },
            new Video() { Title = "MyTitle2", Length = 270 }
            );

        context.SaveChanges();
        }

Carmarthenshire answered 15/4, 2015 at 12:33 Comment(0)

Q

1

Run Visual Studio as Administrator and put your SQL SERVER authentication login (who has the permission to create a DB) and password in the connection string, it worked for me

Questionable answered 20/8, 2016 at 22:12 Comment(0)

P

1

Step 1: Disconnect from your local account.

Step 2: Again Connect to Server with your admin user

Step 3: Object Explorer -> Security -> Logins -> Right click on your server name -> Properties -> Server Roles -> sysadmin -> OK

Step 4: Disconnect and connect to your local login and create database.

Parsimonious answered 31/3, 2019 at 8:13 Comment(0)

M

1

In my case, I was using Server Credentials and Trusted_Connection=True; at the same time that was throwing the same error above. I changed my connection string from:

"DefaultConnection": "Server=SHRIKANT-NEWPC2;User Id=LocalDev;Password=123;Database=WebDB;Trusted_Connection=True;TrustServerCertificate=True"

To:

"DefaultConnection": "Server=SHRIKANT-NEWPC2;User Id=LocalDev;Password=123;Database=WebDB;TrustServerCertificate=True"

Metencephalon answered 8/6, 2023 at 2:38 Comment(0)

D

0

Check that the connection string is in your Web.Config. I removed that node and put it in my Web.Debug.config and this is the error I received. Moved it back to the Web.config and worked great.

Disbursement answered 23/8, 2015 at 1:0 Comment(0)

G

0

I have no prove for my solution, just assumptions.

In my case it is caused by domain name in connection string. I have an assumption that if DNS server is not available, it is not able to connect to database and thus the Entity Framework tries to create this database. But the permission is denied, which is correct.

Gaunt answered 27/10, 2016 at 10:43 Comment(1)

In my case, this answer is correct, I had a slightly wrong database name (for example, my_DB when it should be myDB), and the only error I received from EF was "CREATE DATABASE permission denied in 'master' ! ** How ridiculous, I had to spend hours to track this down, when EF should have told me 'my_DB' does not exist ** – Passive 14/8, 2020 at 8:41

O

0

If you are using .\SQLExpress as Data Source, you can add "User Instance=True" attribute to your connection string, to fix the error mentioned in the title.

For example,


Data Source=.\\SQLExpress;Integrated Security=true;  
User Instance=true;  
AttachDBFilename=|DataDirectory|\app_data\Northwind.mdf;  
Initial Catalog=Northwind;

User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. Each instance runs under the security context of the individual user, on a one-instance-per-user basis.

Reference: MSDN Link for SQL Server Express User Instances

Odont answered 17/6, 2021 at 16:48 Comment(0)

F

0

This is so wrong - never over-elevate your permissions (use SA) where you don't need to do so.

To create database all you need is: CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission as per documentation or the login to be a member of the dbcreator server level role.
Next - you need to make sure that mssql service login (NT Service\MSSQLServer by default) has permission to modify the file system in the location where you want to create your database (usually 1 .mdf file for data pages and 1 .ldf file for database logs).
Then make sure you know where you create your databases! EF by default sends the laziest query possible, defining only database name: CREATE DATABASE [db_name] and then assuming all of the rest - applying default settings. Make sure you either change these to reflect locations mssql engine service has access to or elevate service permissions. Either way this modification requires mssql restart tyo apply the setting.
Finally, make sure that you connect to the mssql using that login. If you perform an EXECUTE AS USER statement to switch your login it will fail. This method allows only to interpersonate DB user, not the server level login. An attemp of doing it will give you CREATE DATABASE permission denied in database 'master' error message.

Frankel answered 9/5, 2022 at 13:2 Comment(0)

C

-1

The solution to this problem is as simple as eating a piece of cake.This issue generally arises when your user credentials change and SQL server is not able to identify you .No need to uninstall the existing SQL server instance .You can simply install a new instance with a new instance name . Lets say if your last instance name was 'Sqlexpress' , so this time during installation , name your instance as 'Sqlexpress1' . Also don't forget to select the mix mode (i.e Sql Server Authentication & Windows Authentication) during the installation and provide a system admin password which will be handy if such a problem occurs in future. This solution will definitely resolve this issue. Thanks..

Culet answered 13/2, 2017 at 7:29 Comment(0)

K

-1

The solution that worked for me was to use the Entity Framework connection string that is created when I ran the database first wizard when creating the edmx file. The connection string needs the metadata file references, such as "metadata=res:///PSEDM.csdl|res:///PSEDM.ssdl|res://*/PSEDM.msl". Also, the connection string needs to be in the config of the calling application.

HT to this post for pointing me in that direction: Model First with DbContext, Fails to initialize new DataBase

Kinesics answered 24/4, 2018 at 13:35 Comment(0)

L

-1

For me I just close all current session including the SQL Server Management Studio and then I reopened execute the script below works fine

IF EXISTS (SELECT NAME FROM master.sys.sysdatabases WHERE NAME = 'MyDb')
DROP DATABASE mydb RESTORE DATABASE SMCOMDB FROM DISK = 'D:/mydb.bak'

Linesman answered 1/9, 2018 at 18:2 Comment(0)

D

-1

I had the same problem and I tried everything available on the internet. But SSMS RUN AS ADMINISTRATOR work for me. If you still face some issue, make sure you must have downloaded the SQL SERVER.

Dipteran answered 27/1, 2019 at 13:11 Comment(0)

M

-1

the reason for this error may be originate from forwarding of version dependent localdb in visual sudio 2013 to the version independent localDB in VS 2015 onwards, so simply change your web.config file connectionStrings from (localDb)\v11.0 to (localDB)\MSSQLLocalDB and it will certainly work. and this is a good explaination for that Version independent local DB in Visual Studio 2015

Marelya answered 17/11, 2019 at 16:48 Comment(0)

S

-1

To get permission to create database in your local account follow the below given steps.

Disconnect from your local account.
Again Connect to Server with Login : sa and Password : pwd(pwd given to your local login).
Object Explorer -> Security -> Logins -> Right click on your server name -> Properties -> Server Roles -> sysadmin -> OK
Disconnect and connect to your local login and create database.

P.s: For me even without connect/disconnect to server, it worked!

Santosantonica answered 16/9, 2022 at 9:30 Comment(0)

I

-1

I had the same issue, I tried couple of other methods, but none of them worked. I just simply exit the SSMS and run it as an administrator and it worked perfectly.

Ianthe answered 23/12, 2022 at 21:41 Comment(0)

P

-1

In My case , I changed the Authentication Mode from Sql Server Authentication to Windows Authentication and it worked just fine.

Pathoneurosis answered 28/3, 2023 at 10:29 Comment(0)

D

-1

For the connect from Microsoft SQL Server Management Studio add server name like
(localdb)\MSSQLLocalDB

If not working try to create db from Visual Studio > View > SQL Server Object Explorer > Add Database

same server name you can copy to the SSMS and connect and Enjoy!

Diverticulosis answered 29/9, 2023 at 19:28 Comment(0)

D

-13