Can't create database in a specified folder using Sql Server Express?

Question

0.00/5 (No votes)

See more:

I can create a database easily in Sql Server Full edition but not in Sql Server Express and the reason seems to be 'right to access' the specified folder or in other words, that folder seems to be invisible to Sql Server Express services.
For example, CREATE DATABASE MYDB
ON PRIMARY (NAME = 'Chan doi vl', FILENAME = 'd:\MyDb.mdf')
will not be successfully executed with an error telling something like '...operating system error 5 (Access is denied)...'
I have googled for the solution for hours and I guess I have to change something to make sql server express services have right to access but I can't find out how to do and if that is possibly achieved, can we use code (sql statement) to do that instead of using Enterprise Manager?
Please help me out!
All your helps would be high appreciated!
Thank you very much!

Posted 12-Dec-11 2:00am

King Boy

Updated 12-Dec-11 4:23am

Dave Kreskowiak

v3

Add a Solution

Comments

[no name] 12-Dec-11 8:07am

Any mods please edit my question tag to 'database,c#' . I can't do that and also can't understand why.
Thank you!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2011-12-12T04:28:00

Solution 1

Simple: it's a root folder. Vista and above severely restrict the right to create files in the root folder. Change to a subfolder and you should have no such problems.

Posted 12-Dec-11 4:28am

OriginalGriff

Comments

[no name] 12-Dec-11 12:20pm

Thank you! But I have tried with some subfolders such as C:\Documents and Settings\Administrator\Desktop , C:\Program files ...
and there is nothing different except that error.
Thank you very much!

[no name] 12-Dec-11 12:37pm

The only folder that I can create my database in is the one containing system databases like master, model ... (it is MSSQL.1 or MSSQL.2 ... but I don't remember its name exactly).
I forgot to try creating new database using Enterprise Manager but it seems that all tries with using sql statement to create new database are failed (except the case I mentioned above).

King Boy · Accepted Answer · 2011-12-14T08:15:00

I found my own solution, in fact there is no difference about access right between Express Edition and others. My Sql Server Express Services couldn't access the local physical disks because of their limited-privilege accounts which are type of 'Network service'. I changed the account type to 'Local system' and it works well. You simply only need to right-click on server name on the left panel in the manager window, select 'Properties', a new dialogue appears then you select 'Log on' tab and you can choose the expected type of account for the services.