Database creation with SMO in SSE

Hi there.

I tried finding any info regarding this approach and I can't find any, so here I am (again!).

I'm trying to create a database using SMO on a freshly installed SQL Server Express (not going through the advanced install, not going through the surface area configuration, so a lot of things are "wrong").

I get an error

- use SMO to try and create a database; this is the pseudo-code combo used:

1. create new Smo.Database object;

2. create new primary file group and data file for that database (using Smo.FileGroup and Smo.DataFile); add them to the database.

3. invoke Create(). catch exception with the following details:

Exception details:

Microsoft.SqlServer.Management.Smo.FailedOperationException was caught
HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476"
Message="Create failed for Database 'NewDatabase'. "
Source="Microsoft.SqlServer.Smo"
Operation="Create"
StackTrace:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
at Microsoft.SqlServer.Management.Smo.Database.Create()
at Penta.Core.Library.Sql.Classes.Penta_SqlDatabase.Create() in C:\Documents and Settings\[...]

InnerException:

{"Directory lookup for the file \"C:\\Documents and Settings\\[...]\\NewDatabase.mdf\" failed
with the operating system error 5(Access is denied.).\r\nCREATE DATABASE failed. Some file names listed could not be created. Check related
errors."}

Seems like user rights error.

Long story short, the only way the creation step works is if I change the Log on for the SQL Express service from the default Network Service to Local System (either through code or not).

Now, I know LocalSystem is not a wise idea, but it seems to be the only way.

I know it's probably something stupid, but what am I missing?

Thanks you in advance

Hi Calin,

You're right, it's a permissions error. Network Service does not have permissions to read and write files in your User Profile directorys (i.e. the stuff under %My Documents%). This is by design as SQL Express runs as Network Service is the default Service Account for SQL Express. Local System does have permissions to your user profile direcotries, so changing the Service Account results in success. You could have also...

Changed the Service Account to any other account that has permissions to the directory where you want to create your files.|||

Hi, Mike, and thank you for your reply. Your comments make perfect sense, so I'll try and circumvent the problem.

Theoretically, my app would be ran from client machines, with the database on a file server. However, it can also be used locally (small company, single user, etc.). Since more than one user can access the same data in the same time, I don't think User Instances would be a good choice for me.

I do plan on shipping SQL Express with the app, however the users can choose to get SQL Server and use it instead, so using User Instances is not an answer because of that, either.

Of all, it seems the best way would be to change the "default" folder where that data is created, the issue is that the user can choose where they want the database file(s) to be created, so they may specify My Documents :-) In the case of a real shop with someone who knows what they're doing this will most likely not happen, however, the application is suppose to be easily accessible for all users, regardless of their abilities.

Thank you again for your help,