Sql file permissions

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

Hope someone on here can help...

Our C: drive on one of our servers is getting a bit full so we decided to try to relocate the SQL databases to another drive on the same machine. SQL 2016 - Windows Server 2016. Note that this is a virtual server running under Hyper-V.

We followed the instructions to Detach the database, then copy the files to the desired location and then re-attach the database.

This all seemed to work except the database was set to 'Read Only'.
When trying to set read only to False in the options we got an error saying that there were insufficient permissions and the state could not be changed.

Reading a million posts about this everyone suggests that we need to add full permissions to NT Service/MSSQLSERVER.
That sounds reasonable but the security tab does not recognise ant NT Service entries.

Looking at the running services it definitely uses this as the service log on so why can we not select it when trying to allow full access to the moved files?

We ticked the box to show service accounts but still nothing.

What stupid mistake have I made this time?

Thanks

What I have tried:

Tried to add permissions via the file > properties > security settings but cannot find the SQL server account.

Posted 19-Jun-17 3:56am

dnibbo

Updated 19-Jun-17 5:37am

Add a Solution

Comments

[no name] 19-Jun-17 12:03pm

Most probably you allready read this, who knows: Configure File System Permissions for Database Engine Access | Microsoft Docs[^]

dnibbo 20-Jun-17 4:05am

Hi thanks, not sure if it was that exact article but read several similar ones. Our problem is that the NT Service accounts don't show up when we click the 'Check names' button.
We even tried the 'Advanced' option and then clicked on 'Find Now' which shows all available accounts; again the NT Server ones do not show up.

1 solution

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)

RickZeeland · Answer 1 · 2017-06-19T04:12:00

Solution 1

In the past I always found that copying SQL Server database files directly was looking for trouble, instead it's better to make a backup and restore that.

This StackOverflow answer might be what you are looking for: Best way to copy a database (SQL Server 2008) - Stack Overflow[^]

Posted 19-Jun-17 4:12am

RickZeeland

Updated 19-Jun-17 6:54am

v2

Comments

dnibbo 20-Jun-17 4:06am

But when you create a new database it will be in the default directory and therefore still need to be moved wouldn't it?

As it happens, SQL was able to read the data but not update it so we surmised that it was using the server\users permissions.
We changed that to full control and could then set read only to False.

Not entirely sure what the implications are here but it seems to work OK?

Thanks