Error 5123 When Attaching Database

zorvek (Kevin Jones)
zorvek (Kevin Jones) used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
If it is error 5123, then definitely a permission issue, kindly give the user necessary access at the database files level to resolve the issue.
Kindly go through the below links to get complete idea about the issue and how to resolve it out..
https://blog.sqlauthority.com/2017/09/21/sql-server-fix-msg-5123-level-16-create-file-encountered-operating-system-error-5/
https://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Since all files have the same security settings,  I doubt that you are correct, Raja.

Kevin, the error message should show details like access denied, which should give a better clue.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Since all files have the same security settings,

Slight correction, those that aren't working for Kevin is from other machine have SQL 2014.. So, its from other machine and chances are that it has some permission issues.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
It's worth checking, that's true.
Top Expert 2008
Commented:
I'm still not sure what is going on but I got it working without running SSMS as admin. Earlier, when I changed ownership from MSSQLSERVER to my profile, I got the same error. Then I noticed that, in the Security tab of the Properties dialog, when I selected OWNER RIGHTS in the "Group or user names" section, the listed permissions were empty for both Allow and Deny. I had been clicking Advanced and, from that view, OWNER RIGHTS had all permissions checked. This time I clicked Edit and saw that all check boxes were not set so I set all for Allow. That worked. I am not skilled in Windows security so I have no idea what is happening but I am back in business. And I still don't understand why, when all DBs were owned by MSSQLSERVER, the one database did attach while the other two did not despite them appearing to have the same set of permissions.
Mukesh ChandraAssociate Consultant

Commented:
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
Bharat BhushanSolution Manager

Commented:
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/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial