Database in RECOVERY PENDING state - Access Denied

  • angelinemarren

    SSC Veteran

    Points: 284

    Hi
    I have s SQL2016 instance with 8 out of 10 databases in recovery pending state and I need assistance.

    I had just installed an instance of SQL 2017 and restarted the server.  Error log says OS error 5 (Access Denied).  I restarted the SQL services and it has not helped.
    I have tried taking them offline and back on but it doesn't help, it goes back to Recovery Pending. 
    I have tried:
    ALTER DATABASE DBname SET EMERGENCY;
    GO
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file "E:\Databases\STR_Main.mdf". Operating system error 5: "5(Access is denied.)".

     
    ALTER DATABASE  DBname set single_user
    GO
    ALTER DATABASE  (DBname , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;DBCC CHECKDB (
    GO
    ALTER DATABASE DB DBname set multi_user
    GO
    ALTER DATABASE DBname SET ONLINE
    GO
    All 10 databases are in the same folder on the E drive. 
    I tested creating a new database and got this error:

    MSSQL$MSSQLSERVER2016 has permissions to the 2 databases that are working ok but not those that are in recovery pending. 
    I have tried to give permissions to this account but it doesn't recognise it. 

    Anybody seen this before or have any advice?

    Thanks
    Ange

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Could you elaborate why these databases would be in recovery pending state ?
    Apparently the SQLServer service account cannot access the folder E:\Databases

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • angelinemarren

    SSC Veteran

    Points: 284

    Hi Johan

    Why is what I am trying to figure out. I don't know why they are stuck at recovery pending.
    Yes correct it does appear that the service account no longer has access, I'd like to know why?  How can this have changed all of a sudden.
    Additionally, how come it has access to some of the databases and not others in the same folder?
    I am trying to give MSSQL$SQLSERVER2016 permissions to that folder but it is not recognising the service. (I have selected the server as the location below)

    Thanks
    Ange

  • angelinemarren

    SSC Veteran

    Points: 284

    For the benefit of anyone that comes across this thread and is experiencing the same issues...

    For some, yet unknown, reason the SQL virtual service account NT SERVICE\MSSQL$MSSQLSERVER2016 lost permissions to the databases.
    My mistake in my last post was that I didn't include the "NT SERVICE\" part.  I had to manually give permissions to this account for each mdf and ldf file.
    I then ran then below and they all came back up no problem.

    ALTER DATABASE DBname SET EMERGENCY;
    GO
    ALTER DATABASE DBname set single_user
    GO
    ALTER DATABASE (DBname , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;DBCC CHECKDB (
    GO 
    ALTER DATABASE DB DBname set multi_user 
    GO
    ALTER DATABASE DBname SET ONLINE
    GO

    If anyone ever finds a reason why the service account would lose permissions like that please do let me know.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    " ... just installed an instance of SQL 2017 and restarted the server.... "  
    chances are, that may have reset the folder / file permissions.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Lynn Pettis

    SSC Guru

    Points: 442144

    To the best of my knowledge permissions aren't just lost.  Someone with appropriate privileges may have made some changes not realizing what affect those changes may have had.

  • angelinemarren

    SSC Veteran

    Points: 284

    Lynn, I would have said the same however I am the only user accessing this server.
    Johan, Quite possible I guess. I've installed multiple of instances in same set up but never experienced this before.
    Lets hope I never do again 🙂

    Thanks both for your input.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    angelinemarren - Monday, January 7, 2019 8:40 AM

    Lynn, I would have said the same however I am the only user accessing this server.
    Johan, Quite possible I guess. I've installed multiple of instances in same set up but never experienced this before.
    Lets hope I never do again 🙂

    Thanks both for your input.

    You may be the only user accessing the server but that doesn't mean GPO pushed out to your server might have caused the issue.  Again, I haven't heard of permission just getting lost.  Something causes them to go away.

  • Johnson Welch

    SSCoach

    Points: 18748

    The reason of getting error 5120 is that MDF file are not located at their accurate location. It might be the possibility that system drives might not be having permission to store server file within them. Take a look on troubleshooting the error SQL error 5120: 
    https://quickdata.org/blog/operating-system-error-5-sql-server-error-5120/

  • angelinemarren

    SSC Veteran

    Points: 284

    Lynn Pettis - Monday, January 7, 2019 9:22 AM

    angelinemarren - Monday, January 7, 2019 8:40 AM

    Lynn, I would have said the same however I am the only user accessing this server.
    Johan, Quite possible I guess. I've installed multiple of instances in same set up but never experienced this before.
    Lets hope I never do again 🙂

    Thanks both for your input.

    You may be the only user accessing the server but that doesn't mean GPO pushed out to your server might have caused the issue.  Again, I haven't heard of permission just getting lost.  Something causes them to go away.

    Very good point. Given that I rebooted the server it likely picked up a GPO that was waiting to be applied.  Will speak to IT about that thank you!

  • Jeff Moden

    SSC Guru

    Points: 994951

    I've see it quite often.  I think it most likely that the SQL Server Service tried to come up before the drive connections were up. 

    If you go to SERVICES and lookup the SQL Server service, you can change the Startup Type from Automatic to Automatic(Delayed Start) and that should prevent future occurrences of this problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • angelinemarren

    SSC Veteran

    Points: 284

    Jeff Moden - Tuesday, January 8, 2019 10:02 PM

    I've see it quite often.  I think it most likely that the SQL Server Service tried to come up before the drive connections were up. 

    If you go to SERVICES and lookup the SQL Server service, you can change the Startup Type from Automatic to Automatic(Delayed Start) and that should prevent future occurrences of this problem.

    That's an interesting thought, thanks Jeff. Sounds like the most likely answer. Going to see if I can cause it to prove it 🙂

  • Jeff Moden

    SSC Guru

    Points: 994951

    angelinemarren - Wednesday, January 9, 2019 4:27 AM

    Jeff Moden - Tuesday, January 8, 2019 10:02 PM

    I've see it quite often.  I think it most likely that the SQL Server Service tried to come up before the drive connections were up. 

    If you go to SERVICES and lookup the SQL Server service, you can change the Startup Type from Automatic to Automatic(Delayed Start) and that should prevent future occurrences of this problem.

    That's an interesting thought, thanks Jeff. Sounds like the most likely answer. Going to see if I can cause it to prove it 🙂

    It also explains why some databases came up fine... the drives were finally available.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • prettsons

    SSCertifiable

    Points: 7437

    Whenever a database is detached, the permission of the database files is modified. If any SQL Login has detached the database, then permissions are given to SQL Server Service account. If any Windows account has detached the database, then the permission is given to the account who has detached itself.

    • Right-click on the file which is mentioned in the error message and go to properties.
    • In the Properties dialog box, go to Security tab.
    • In the Select Group or user names box (which has sysadmin in above image). Click the Edit button and enter the name you used to login to SQL Server. If its windows account, then use that account else use service account.
    • Click Check Names and verify that the name you are specifying does exist as SQL Server login.
    • Click OK.
    • In the Group or User Names box, select the user name you selected in the above step and give full control permission to the user. Click OK.

    You can also check this blog: https://bit.ly/2H4zzbo

    SQL Database Recovery Expert 🙂

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply