CREATE FILE encountered operating system error 5 (Access is denied.)

Question

I'm trying to execute the following script in SQL Server Management Studio:

USE [master]
GO
CREATE DATABASE [test1] ON PRIMARY (
  NAME = N'test1', 
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf',
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
  NAME = N'test1_log',
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1_log.ldf',
  SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

But I'm getting the error:

Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf'.

Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Already have all role permissions for my user, any ideas on what's wrong?

"Your user" or the user your service is running as? The server process will try to create that file, not your user. — , Aug 09 '12 at 20:23
Could you check if the file already exists? (Perhaps from previous attempts) If exists and is opened by SqlServer you get an access denied error — , Aug 09 '12 at 20:25
As @BoeroBoy noted, please check the service account ("Log On As" in services.msc). Then ensure that this account has access to this directory. Finally, if this is Vista/Win7 with UAC enabled, try opening SSMS as Administrator (rt-click). — swasheck, Aug 09 '12 at 20:43
Services->Sql Server->Double Click->Second Tab Page (Connection?) — , Aug 09 '12 at 21:02
@thiagocfb Start->Run->services.msc Scroll through the list of services until you find SQL Server. Right-Click->Properties->Log On tab — swasheck, Aug 09 '12 at 21:02
Also, have you tried just running a plain CREATE DATABASE [test1]; GO? — swasheck, Aug 09 '12 at 21:03
You guys were right, checked which user was being used by the service and it's working now, thanks !! :) — , Aug 09 '12 at 21:32
Was this instance upgraded from SQL Server 2008, or was it a fresh install of 2012? — Jon Seigel, Aug 29 '12 at 01:41

score 24 · Accepted Answer · answered Aug 10 '12 at 08:03

24

You are getting a permissions error. The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files.

You need to give the account which is running SQL Server (not your account) full control of C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA.

answered Aug 10 '12 at 08:03

mrdenny

26,988
2
42
81

Yep, the problem was the user that was running the service ! :) Thanks ! – thiagocfb Oct 18 '12 at 15:31
In general SQL Server windows service run under Network Service account. So appropriate rights will have to modified on the respective directories for this account if that's the case on your PC as well. – RBT Aug 12 '16 at 01:42
5

At least when running the SSMS gui as a non-admin user, for the initial attach operation, the userid running SSMS must ALSO have permissions in that folder. Running SSMS as administrator DURING THE INITIAL ATTACH solves this, and then you can run as non-admin later without issues. – tbone Aug 28 '17 at 21:52
In my case, the service account had access to the folder but I was still getting the error. Restarting the service fixed the issue. The permissions were not originally there but were added before the restart but after the error. Hope this helps someone who is at their wits end! – Jana Sattainathan May 21 '21 at 17:47
This worked for me, I had to change the security access for the files themselves mdf and ldf to "Everyone" full control .. and it worked ( my issue was on personal computer, so no issues giving access to everyone) – asmgx Feb 13 '22 at 17:13

score 7 · Answer 2 · answered Aug 09 '12 at 22:06

Based on our comment thread it sounds like you may have gotten yourself a bit sideways during install. The installer allows you to choose your default data directory and (I would assume) sets the appropriate permissions on that directory for the service account that you specified.

In your CREATE DATABASE statement you're specifying a location, but was that location the one that was specified in the original setup? Has the service account changed?

A way to test this is to just run a generic

CREATE DATABASE [test1]; 
GO

If you get the same error then perhaps the service account has changed or something about the NTFS permissions has changed.

A resolution path (also based on comment string) is to confirm that the service that is running SQL Server has R/W permissions on the path that you're specifying. To do this:

Start->Run->services.msc->scroll through the list of services until you find SQL Server->right-click->properties->Log On tab

Now go and ensure that account has the appropriate permission on that directory to do what it needs to do.

score 2 · Answer 3 · answered Aug 09 '12 at 20:23

2

Seems there are incorrect number of spaces in the supplied path, so, it is not matching folders tree.
Sql server won't create an non existing path.

Edit:
Your original post says:

...\Microsoft SQL         Server\...
...\Microsoft SQL     Server\...

and I guess this are not existing paths, and as they are sorounded by colons, it is relevant how much spaces are there.

answered Aug 09 '12 at 20:23

Saic Siquot

143
4

incorrect number of spaces ? where ? if i copy and paste this path on my file explorer it will reach this folder just fine @__@ – Aug 09 '12 at 20:38
@thiagocfb so when you open the path do you see those files? – swasheck Aug 09 '12 at 20:45
in your "paste" you have 9 chars in one case and 5 chars in the other, between Microsoft SQL and Server, and this was strange to me because normaly it should be only one space character. It is not visible now because your post was edited by @marc_s – Aug 09 '12 at 20:46
@swasheck I see several .mdf files, not test1.mdf, it's the one I'm trying to create with this query – Aug 09 '12 at 20:53
@Luis Siquot oh I see, must have been a mistake of mine while setting the query to be shown as code, thanks for the heads-up ! – Aug 09 '12 at 20:54

score 0 · Answer 4 · answered Aug 10 '12 at 06:25

The above script that you post in your question section is correct. It might be possible that the file path that you mention in FILENAME could be wrong.

Please use the script given below. It simply works then make sure the file path that you use in your script.

Use Master
go
CREATE DATABASE test1 ON  PRIMARY 
( NAME = N'test1', 
  FILENAME = N'D:\test1.mdf', 
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1014KB)
 LOG ON 
( NAME = N'test1_log', 
  FILENAME = N'D:\test1_log.ldf', 
  SIZE = 164671KB , MAXSIZE = 1048GB , FILEGROWTH = 10%)
GO

CREATE FILE encountered operating system error 5 (Access is denied.)

4 Answers4

Linked