“Access denied” when attaching a database via SSMS

This post outlines a few ways to fix the access denied error you might encounter when attaching a database via SSMS in certain conditions.

Situations when I ran into this error:

• SQL Server reinstall.
• Software vendor sent the database files for you to attach.
• Just wanting to move or copy the mdf and ldf files between servers (although you should use backup & restore instead).
• The original VM dying and the Windows/Server team mounting the SQL Server disk(s) to another VM for recovery purposes.

Either way, 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)

Not the cause

As mentioned above, 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:

• On the data and transaction log directories

• Or directly on the files themselves

If you want to read more about SQL Server file access permissions, check out this post.

The actual cause

This happens when attaching the database files 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, generally SQL Server’s database directories have stricter permissions that are inherited by the files inside them.
You either need to give yourself the required permissions on the files or to run SSMS as administrator.

The fixes

All these fixes are performed on the SQL Server instance’s host.

Run SQL Server Management Studio as administrator

On the host of the instance to which you’re trying to attach the files, open SSMS as Administrator (right click on SSMS > Run as Administrator), and the GUI will no longer error out due access denied.

Grant your AD/Windows account permissions on the files

Open Command Prompt or PowerShell as Administrator, and use icacls to grant your user full control over the mdf and ldf files.

After the permissions change you should be able to attach the database from SSMS.

Use sqlcmd in elevated Command Prompt or PowerShell

You can skip SSMS altogether and just open Command Prompt as Administrator, and use sqlcmd and T-SQL to attach the database:

Conclusion

The “access denied” error when attaching a database can be easily avoided.
Regardless of which option you pick, make sure you’re doing it on the instance’s host