Link to home
Start Free TrialLog in
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)Flag for United States of America

asked on

Error 5123 When Attaching Database

Three databases: one restored with 2017, two created with 2014. I can attach the first but not the second two - I get a 5123 error. All three have the same security settings. All three are in the same directory and SQL Server has access to that directory.
SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To solve this problem let us create a sample scenario. We have two users – User1 and User2. Here, User1 and User2 are working on the SQL Server 2008 R2 database. Both have administrator credentials and are able to perform the operations of attaching and detaching databases. User1’s set of administrator credentials is used to detach the database and User2’s set of administrator credentials are used to attach the database.

Use User1 account to detach the stats1 database:

EXEC sp_detach_db ‘stats1’

With User2’s account, try to attach the stats1 database by using the following command:

CREATE DATABASE stats1 on (FILENAME = ‘E:\MSSQL2008\DATA\stats1.mdf’),  (FILENAME = ‘E:\MSSQL2008\DATA\stats1.ldf’) FOR ATTACH ;

Note: Update correct path values in above command as per your system settings.

The system displays the following error message:

Unable to open the physical file “E:\MSSQL2008\DATA\stats1.mdf”, operating system error 5: “5: (Access is denied)”

Right-click on the file stats1.mdf and select Properties.
Check the permissions of the MDF file. The system displays a list of user accounts in the Groups and the user name field. The error is displayed when only User1’s account is allowed to perform attach-detach operations on the database.
On the stats1.mdf Properties window, click Add. Add the user credentials for User2, and select the Full Control
Click
Try to attach the file to the database by using User2’s credentials.

For detailed information, you may refer:

https://blog.sqlauthority.com/2017/09/21/sql-server-fix-msg-5123-level-16-create-file-encountered-operating-system-error-5/

http://www.data-recovery-solutions.com/blog/resolve-microsoft-sql-server-error-824/

https://www.stellarinfo.com/blog/sql-server-2008-r2-database-error-5123/


Thanks
Mukesh Chandra
In this situation, user can try to attach the inherited .MDF file via SSMS or T-SQL

Complete information is available here: https://www.stellarinfo.com/blog/sql-server-2008-r2-database-error-5123/