Permission denied when trying to create database at a specified file location

Question

I just installed SQL Server 2012 Express, and I'm running a C# program which attempts to create a database at a specified location using the following command:

create database [MyDatabase] 
    on primary (name=N'MyDatabase.mdf', 
        filename=N'C:\Users\myName\Documents\MyDatabase.mdf') 
        log on (name=N'MyDatabase_log.ldf', 
        filename=N'C:\Users\myName\Documents\MyDatabase_log.ldf')

This fails, both from the program and from management studio with the following error:

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "C:\Users\mikea_000\Documents\MyDatabase.mdf" failed with the operating system error 5(Access is denied.).
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

What steps to I need to give SQL Server permission to access this directory? I am connecting via integrated security (Windows Authentication in SSMS), and my user account is an administrator on my machine.

I can create and drop databases without specifying a file name with no issue:

CREATE DATABASE foo

ArunPratap · Accepted Answer · 2018-08-11 14:00:37Z

2

Just add permissions to the user "NT Service\MSSQL$SQLEXPRESS" Or check the user name in services.msc, SQL Server (SQLEXPRESS) service

edited Aug 11, 2018 at 14:00

ArunPratap

5,0807 gold badges28 silver badges45 bronze badges

answered Feb 9, 2014 at 20:07

Roman Pokrovskij

9,82421 gold badges101 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ChaseMedallion Over a year ago

I checked the user name and it is as you state. Now, how do I go about adding permissions for that user?

Roman Pokrovskij Over a year ago

Add all rights for this user to operate in the folder C:\Users\mikea_000\Documents\

amuliar Over a year ago

I solved this problem by granting permission to "NT Service\MSSQLSERVER"

alelom Over a year ago

A better step-by-step guide at the answer below stackoverflow.com/a/21665509/3873799.

E.J. Brennan · Accepted Answer · 2014-02-09 21:58:57Z

1

I think you need to grant sql server, not the user, permissions to use the target directory if its not in the default location.

The Database Engine service must have permission of the Windows file system to access the file folder where database files are stored. Permission to the default location is configured during setup. If you place your database files in a different location, you might need to follow these steps to grant the Database Engine the full control permission to that location.

http://msdn.microsoft.com/en-us/library/jj219062.aspx

edited Feb 9, 2014 at 21:58

answered Feb 9, 2014 at 21:49

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

1 Comment

alelom Over a year ago

This is the right answer. I had to add "Full control" permission for the default database instance for this to work. In summary: use File Explorer to go to the folder where you would like to create your Database; right click on it; Properties; Security; Edit; Add; Enter NT SERVICE\MSSQLSERVER; Check Names; OK; Assign Full Control; OK.

JGilmartin · Accepted Answer · 2018-03-13 15:31:47Z

1

I had the same issue.

to resolve, right click on each of your .LDF and .MDF files and click security, then for your current user (mine was "Users (JG-LAPTOP\Users)") tick the box "full control".

go back to SQL Server and then retry, it will now attach sucessfully

answered Mar 13, 2018 at 15:31

JGilmartin

9,38814 gold badges72 silver badges90 bronze badges

Comments

Ayanda Makhathini · Accepted Answer · 2022-03-05 19:14:16Z

0

Removing AttachDbFilename=|DataDirectory|[DatabaseName].mdf in the Web.Config helped me. I think when the IDE tries to create the database in the project folder as opposed to the folder it already has access to such as C:\Users{UsernameFolder} then it throws error.

answered Mar 5, 2022 at 19:14

Ayanda Makhathini

1

Collectives™ on Stack Overflow

Permission denied when trying to create database at a specified file location

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related