Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:\sqlserver\test.bak';
go
restore database msdbtest from disk='c:\sqlserver\test.bak' 
    with move 'MSDBData' to 'C:\SQLServer\Data\msdbtest_data.mdf'
    , move 'MSDBLog' TO 'C:\SQLServer\Logs\msdbtest_log.ldf';
go

I then stopped the SQL Server Service, made a copy of C:\SQLServer\Data\msdbtest_data.mdf and C:\SQLServer\Logs\msdbtest_log.mdf, and restarted SQL Server.

I then performed the following:

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

This results in:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "c:\sqlserver\data\msdbtest_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:

enter image description here

Security effective access for the SQL Server service account on c:\sqlserver\data\msdbtest_data.mdf is:

enter image description here

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:\SQLServer\Data 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: )
share|improve this question
    
Is this a case-sensitive instance? –  Jon Seigel Nov 22 '13 at 3:23
    
server collation is the default for an EN-US installation. So it is CI, AS. –  Max Vernon Nov 22 '13 at 3:25
    
Are the .mdf and .ldf in the file system read-write (i.e., not marked read-only)? –  Jon Seigel Nov 22 '13 at 3:28
    
Mike Walsh helped determine the issue appears to be resolved by running SSMS 'as administrator'. –  Max Vernon Nov 22 '13 at 3:29
2  
That solution makes no sense. I'd love to hear why it works. :) –  Jon Seigel Nov 22 '13 at 3:30

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
I'm thinking SQL Server is built this way on purpose to "protect" the potential user from attaching a maliciously designed database. However, that is only hypothetical. –  Max Vernon Nov 22 '13 at 3:47
    
I actually did check the ownership of the .mdf in question after getting error 5, and changed it to NT Service\MSSQLSERVER - this did NOT help. –  Max Vernon Nov 22 '13 at 3:48
    
No read the article again. Look at the permissions after YOU run detach while not logged in as UAC. Then look at the permissions of a similar MDF with SQL Server service turned off. See the difference? That's it. When you run as administrator you are impersonating the admin admin, like you clicked allow access when clicking into a folder for the first time to impersonate admin. –  Mike Walsh Nov 22 '13 at 3:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.