Access Denied error in CREATE DATABASE … FOR ATTACH

I’m attempting to attach a copy of msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to recreate missing jobs out of a foreign msdb.

I performed the following actions:

use master;

backup database msdb to disk='C:sqlservertest.bak';
go
restore database msdbtest from disk='c:sqlservertest.bak' 
    with move 'MSDBData' to 'C:SQLServerDatamsdbtest_data.mdf'
    , move 'MSDBLog' TO 'C:SQLServerLogsmsdbtest_log.ldf';
go

I then stopped the SQL Server Service, made a copy of C:SQLServerDatamsdbtest_data.mdf and C:SQLServerLogsmsdbtest_log.mdf, and restarted SQL Server.

I then performed the following:

drop database msdbtest;
go
create database msdbtest on (
    name='msdbtest_data'
    ,filename='c:sqlserverdatamsdbtest_data.mdf'
) log on (
    name='msdbtest_log'
    , filename='c:sqlserverlogmsdbtest_log.ldf'
) for attach;

This results in:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "c:sqlserverdatamsdbtest_data.mdf". 
Operating system error 5: "5(Access is denied.)".

SQL Server is configured with the default out-of-the box settings for the service account:

Security effective access for the SQL Server service account on c:sqlserverdatamsdbtest_data.mdf is:

I am at a total loss; I think the error being returned is misleading me. Perhaps I misunderstand the purpose of CREATE DATABASE ... FOR ATTACH?

As far as I can tell, SQL Server should certainly have access to the file. The folder C:SQLServerData is used by all the other user databases currently attached to the instance.

SELECT @@VERSION returns:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
Dec 28 2012 20:23:12 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

When you get this message it can be a few things. Some examples:

1.) Read Only Marked Files

2.) Insufficient permissions to the files for the SQL Server Service account

3.) Insufficient permissions to the folders the files live on.

You indicated in our chat that this was not read only. You also verified permissions looked like full access. That ruled out these three items.

The fact that, from our chat conversation, you were not experiencing this issue when you properly detach a database using sp_detach_db – which actually changes permissions around a bit on the files led me to suggest trying to run SSMS as administrator.

Why? Well this article gets into a lot more nitty gritty about some of the impersonation issues here – but my understanding is:

1.) When you just stop the SQL Server Service and move the file around. The owner of the file is the service account that created it.

2.) When you detach the database – you’ll potentially notice a permissions change. The owner has changed basically and the file is not really associated with that SQL Server service account the same way.

3.) So it works when you try to attach because the permissions can get assigned, because the security of the file allows it..

But if you just stop the service the security hasn’t changed, and you can’t attach that if you have UAC enabled unless you are impersonating administrative access.

I’ve probably made the file ownership and access more confusing here but the article I shared probably does a better job 🙂

But one slight moral is – do a detach next time 😉 Or just be prepared to run SSMS as administrator from time to time when interacting with the OS in various ways.

According to the SQL CrossJoin Blog, trace flag 1802 can be used to disable the permissions-modification code.

From that blog:

[Trace flag 1802] Works around a problem where “after you detach a Microsoft SQL Server 2005
database that resides on network-attached storage, you cannot reattach the SQL
Server database. This problem occurs because SQL Server 2005 resets the file
permissions when the database is detached. When you try to reattach the database, it cannot be attached because of limited share permissions.” (KB922804)

From the description, it sounds like this flag disables SQL Server’s functionality in changing permissions on database files
after the DB is detached, which means the flag has security implications.

Stop the SQL Server services and change the “log on as” to Local System

Restart the SQL services and check. It started working fine in my case.