5

I have two databases. Both are in the same folder. Both have, as far as I can tell, the exact same ownership and permissions. I can attach one but not the other - I get an error 5123.

For both files...

  • Owner is MSSQLSERVER

Permission entries:

  • MSSQLSERVER - Full control

  • OWNER RIGHTS - Full control

  • Administrators - Full control

I reviewed the other question referenced below but could not get any of the suggestions to work given my constraints listed above. I posted what finally worked below.

Kevin Jones
  • 69
  • 1
  • 1
  • 6
  • I do not want to change the owner or run SSMS in admin mode. I want to understand why one DB can be attached but not the other. – Kevin Jones Jun 07 '18 at 22:22
  • What is the OS error reported in the 1523 message? Have you compared the ACLs on the database and log files with cacls. eg `PS C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\data> cacls .\mydb.mdf` – David Browne - Microsoft Jun 07 '18 at 22:29
  • CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '...Dababase.mdf'. (Microsoft SQL Server, Error: 5123) – Kevin Jones Jun 07 '18 at 22:33
  • I don't know what ACLs are and I don't have "PS" installed - i.e., entering the above command results in "'PS' is not recognized as an internal or external command, operable program or batch file." – Kevin Jones Jun 07 '18 at 22:36
  • If I change the owner to my profile, I can attach it. But that doesn't help me understand what the current problem is. The other database's owner is MSSQLSERVER and it attaches without issue. – Kevin Jones Jun 07 '18 at 22:39
  • Possible duplicate of [Access is denied when attaching a database](https://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database) – Hoppeduppeanut Jun 07 '18 at 22:40
  • I just tried changing the owner to my profile and I got a little further, but then I get an access violation on the log file. Yes, I changed it's owner too. – Kevin Jones Jun 07 '18 at 22:50
  • I tried running SSMS as admin and, yes, I can attach. I just did that again and took it offline before detaching (per https://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database). Running SSMS in non-admin mode results in the same error. – Kevin Jones Jun 07 '18 at 22:54
  • I was sure this would work: I ran SSMS as admin, attached the DB, quit and started SSMS as regular user, backed up the DB, deleted the DB, restored the DB, took the DB offline, detached the DB, tried to attach and it failed with the same error! – Kevin Jones Jun 08 '18 at 01:13

5 Answers5

2

Add your User to Permissions:

[your user] - full control  

MSSQLSERVER - Full control

OWNER RIGHTS - Full control

Administrators - Full control
seraj
  • 149
  • 1
  • 7
1

All files on the OS platform are subject to access permissions. Remember that for optimal security, DENY always overrides any permissions allowed. The SQL Server database has physical files(.MDF, .NDF, and .LDF) These files just like any other files on the server is subject to access permissions.

In your scenario of SQL Server error 5123, you are attempting to attach a database file to the instance where your credentials do not have the right privileges on the file. The trick here though is that whoever initially detached the database file is automatically set as the owner and no one else.

In order to fix this, you have to give the R/W or FULL control rights to the account attempting to attach the file. Note: If there are multiple files for the database which most likely would be, you need to make sure each file security properties is set properly for the user performing the ATTACH.

You can also read this blog for further details: https://www.stellarinfo.com/blog/sql-server-2008-r2-database-error-5123/

samosql
  • 141
  • 4
0

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.

Kevin Jones
  • 69
  • 1
  • 1
  • 6
0

Open Management Studio with administrator rights (right click, run as Administrator) and try again, in my case, I was able to solve the problem.

Baldie47
  • 1,148
  • 5
  • 16
  • 45
jdalvarez18
  • 1
  • 2
0

Note that the data file extension you see depends on you version of the server. Select the file '*' to view all existing files in the location and choose the data file to attach.

snnpro
  • 193
  • 2