“Access denied” when attaching a database via SSMS

A few ways of fixing the access denied error you’re attaching a database to an instance, either as a result of:

• SQL Server reinstall
• Just wanting to move or copy the mdf and ldf files between servers (you should use backup & restore instead if you have backups available)
• The original VM dying and the Server team mounting the SQL Server disk(s) to another VM for recovery purposes

In certain situations, you’ll run into the “CREATE FILE encountered operating system error 5(Access is denied.)” error message in SSMS even though you’ve granted full control over the files to the SQL Server service account via icacls.

Full error text:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\VSQL\Data\AdventureWorks2017.mdf'. (Microsoft SQL Server, Error: 5123)

Cause

This error is unrelated to the permissions that the SQL Server service account has over the files, so this implies that you’ve already handled that part by running icacls in an administrative command prompt:

Or directly on the files themselves:

This happens when attaching the database files via the GUI and your own user account (either local or AD) doesn’t have explicit full control permissions over the files. Even if your user account is a member of the local Administrators group, because SSMS is not running with elevated privileges, it’s just running as your normal user account.

Fix

There are a few ways of fixing this (each bullet point represents a complete fix for this error):

• Open SSMS locally (on the host of the instance to which you’re trying to attach the files) as Administrator (right click on SSMS > Run as Administrator) and the GUI will no longer error out due to it running with elevated privileges
• Use the icacls example above for granting full control over the mdf and ldf files only, but replace the service account part with your account
  
  Grant full control over the data and tlog files

  MS DOS

  icacls "[DataDirPath\DataFile.mdf]" /GRANT "[Domain\YourUserAccount]":F icacls "[TLogDirPath\TLogFile.ldf]" /GRANT "[Domain\YourUserAccount]":F

  1 2	icacls "[DataDirPath\DataFile.mdf]" /GRANT "[Domain\YourUserAccount]":F icacls "[TLogDirPath\TLogFile.ldf]" /GRANT "[Domain\YourUserAccount]":F

• Although this option no longer relies on SSMS, it’s still valid – open Command Prompt as Administrator and use SQLCMD and T-SQL to attach the database
  
  MySQL

  USE [master] GO CREATE DATABASE [DatabaseName] ON (FILENAME = N'DataDirPath\DataFileName.mdf'), (FILENAME = N'TLogDirPath\TLogFileName.ldf') FOR ATTACH GO

  1 2 3 4 5 6 7

  USE [master] GO CREATE DATABASE [DatabaseName] ON (FILENAME = N'DataDirPath\DataFileName.mdf'), (FILENAME = N'TLogDirPath\TLogFileName.ldf')   FOR ATTACH GO