Detaching a database also alters file permissions

Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).

But when you try to attach the database, you might get something like

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf".
Operating system error 5: "5(Access is denied.)".

The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.

On most database servers, the SQL Server service account is granted full control of the directories that host the database files. It goes without saying that the service account that SQL Server runs on should be able to create, read, write and delete database files. Looking at a sample database on my local server, the .mdf and .ldf files don’t actually inherit permissions from their folder, although the permissions are very similar to that of the folder.

Using Windows authentication

Detaching the database is a simple system procedure call.

EXECUTE master.dbo.sp_detach_db @dbname = N'Playlist';

The permissions on the files have magically changed! Now, only my Windows account (the one I used to log on to SQL Server) has permissions on the files:

The Microsoft documentation on Database Detach and Attach actually states, rather cryptically, that “File access permissions are set during a number of database operations, including detaching or attaching a database.” Whatever that means.

It gets even weirder. Re-attaching the database strangely works, even though SQL Server should theoretically not be able to access the files using its service account.

CREATE DATABASE [Playlist] ON 
( FILENAME = N'E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf' ),
( FILENAME = N'E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist_log.ldf' )
 FOR ATTACH;

There’s a Connect item, where a Microsoft associate explains in one of the comments how this works.

“[SQL Server impersonates] the attacher to validate that they have permissions on the files they are trying to attach to validate that they are not leveraging [SQL Server’s] service account to attach files they do not own.”

Fair enough. In essence, you must yourself have access to the files in order to be allowed to attach them. Otherwise, you could leverage SQL Server to gain access to database files that you otherwise wouldn’t have permissions to.*

Using SQL Server authentication

When using the “sa” login, the permissions of the files don’t change when I detach the database. I suppose that this has to do with the fact that a SQL Server login does not correlate to a Windows account, so who would you assign the ownership and permissions to?

But can we attach a file as “sa”? Absolutely. And the permission set is still unchanged.

Detaching as Windows user, attaching as SQL Server user

Which brings me to the problem that I encountered the other day. Detaching a file as a Windows user will alter the permissions on the files. So if you then try to attach those files as a SQL Server user, the SQL Server service won’t impersonate a Windows user, but will rather try to read the files using the credentials of its service account (which doesn’t have permission anymore, because we detached as a Windows user).

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf".
Operating system error 5: "5(Access is denied.)".

Bottom line

• Detaching a database when logged on with a Windows account will change the file permissions, restricting SQL Server’s service account and only allowing your user to access the files.
• Detaching a database as “sa” or a SQL Server account will leave the files’ permission sets unchanged.
• If you’re connected as “sa” or another SQL Server account, SQL Server’s service account needs permissions on any database files you want to attach.
• If you’re connected as a Windows login, you need sufficient permissions on the files to attach them.