Click here to Skip to main content
15,917,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 19-Jun-17 5:37am
Comments
[no name] 19-Jun-17 12:03pm    
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

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[^]
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900