Resolve Microsoft SQL Server Attach Database Error Code 5120

When the users try to attach a database to SQL server then sometimes it becomes difficult to do this due an error. This error is known as Microsoft SQL Server error 5120 access denied. The error message occurs on the user’s screen is mentioned-below:

error-5120

Error code 5120 occurs, when the account that is running SQL server service does not have the permission to access MDF and LDF files of a database. Therefore, in this post, we are going to discuss the solution to fix the error message.

Fix Attach SQL Database Error 5120

Create two users account such as SQLAdminOne and SQLAdminTwo on the SQL server and then follow the below-mentioned steps to solve the error 5120 in SQL server:

  • Create A New Database: Follow the below-mentioned steps to create a new database such as Test:
  • CREATE DATABASE [Test]
    CONTAINMENT = NONE
    ON PRIMARY
    (NAME = N'Test', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.mdf')
    LOG ON
    (NAME = N'Test_log', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test_log.ldf')
    GO

  • Check Test File Permission For MDF and LDF: Browse the location of MDF and LDF files and then right-click on the file >> Properties >> Security
  • error-5120-2

  • SQLAdminOne Detaches Test Database: Right-click on the Test database>> Tasks >> Detach
  • Check Test File Permission For MDF And LDF Again: Test database MDF and LDF files permissions have changed and full permission are granted to SQLAdminOne only.
  • error-5120-3

  • SQLAdminTwo Tries To Attach Test Database: When SQLAdminTwo tries to attach the Test database SQL error code 5120 occurs due to the limited permission on the database files
  • Grant Full Permission On Both MDF and LDF Files Of Test Database: Follow the below-mentioned steps to grant the full permission on the Test database file:
    • Right-click on the Test database file and then select Properties
    • Now, click on Security tab and then click on Edit button
    • Click on the Add button and then enter the object name to select as NT Service\MSSQL$SQL2013. After that, click on Check Names button and then click on OK
    • Now, give this account Full Control to the file and then click on OK button
    • error-5120-4

  • Reattach Test database: SQLAdminTwo become successful to attach the Test database

Conclusion

Sometimes, the users of SQL server faced MS SQL server error 5120 when they try to attach a database in SQL server. This error mainly arises when the user’s account does not have access permission on a database (MDF and LDF) files. To attach a database file into SQL server, the account that is running on MS SQL server service should have the full access permission on both MDF and LDF files of a database.

Advertisements
This entry was posted in Sql Server and tagged , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s