Wednesday, February 27, 2013

How to Resolve SQL server error 5120: Access is Denied?

At some situation, when a database administrator tries to attach the SQL server database files (.mdf, .ndf or .ldf) to the SQL server, he might get below error message:

Unable to open physical file "E:\BackEnd-accounts\GlDataSQL.mdf". Operating system error 5: "5(access is denied.)". (Microsoft SQL Server: Error 5120)

Reason behind above Error message: The main reason behind above error message is that the account from which you are trying to attach the database files does not have enough privilege for the specific files (.mdf, .ndf or .ldf).

Troubleshoot above Error message: You can troubleshoot above error message by providing the privilege to the account from which your are trying to attach the files. You can give the permission to the account by following:

Run below script to get service account.

Code Snippet
declare @sqlser varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
@value_name='objectname', @value=@sqlser OUTPUT
PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

Now you can provide privilege to the account by following steps:
  1. Press right button of your mouse on the folder E:\BackEnd-accounts.
  2. Now click on properties section and select security tab.
  3. Now click on Add button to add a SQL service account.
  4. Modify privilege setting and click OK button
  5. Make sure all the files have modify privilege.
  6. Now try to attach the database files.
  7. Done
For Example: In my case, I have given the right to the local administrator and problem was solved. For your reference I am attaching an image below.

1 comment:

  1. Thanks the best solution i have read
    it has been solve my same problem

    Amr Swilam


Related Posts Plugin for WordPress, Blogger...