Yesterday we had a power failure and after the power was back our financial databases on the SQL did fall into "Recovery Pending" state. After struggling a while I've remembered that a few days ago we did change the sharing permissions of the database directory to certain users from "everyone" and tried to revert the permissions back to "everyone" and everything was OK again in a few minutes.
What reminded me of this directory share permission change were these error messages in the event log:
FileMgr::StartLogFiles: Operating system error 2(The system cannot
find the file specified.) occurred while creating or opening file
'\\SERVER\xxx.ldf'. Diagnose and correct the operating system error,
and retry the operation.FCB::Open failed: Could not open file \\SERVER\xx._MS for file number
1. OS error: 5(Access is denied.).
Why did this happen?
Best Answer
It sounds like a simple permissions issue.
Why did this happen?
The Everyone permission literally allows anyone to connect to those network shares and access those files. When you remove those permissions, now no one is allowed to access them. What may be confusing is that you may think that SQL Server has access to those files and shares, when, in fact, it does not.
To allow specific access to those databases that run over SMB shares (starting with Microsoft SQL Server 2012), Microsoft recommends that the SQL Server service run as a
<domain name>\<user name>and that this account have FULL CONTROL to the file share AND NTFS permissions to the underlying files and/or directories within that file share.Alternately, Microsoft recommends that if you use a system account instead for the SQL Server service account then apply FULL CONTROL share AND NTFS permissions to the account
<domain_name>\<computer_name>$--where computer name is the computer name of your SQL Server.I'd retry removing those "everyone" permissions once more, in a test environment, to validate that this fixes your issue.
For more details see:
https://msdn.microsoft.com/en-us/library/hh759341.aspx