Access is denied error when attaching a SQL Server database


By:   |   Updated: 2016-12-05   |   Comments (5)   |   Related: More > Database Administration


Problem

Two database administrators are members of the local server Administrators group on a SQL Server machine and they also have sysadmin rights on the SQL Server instance.

The following scenario occurs:

  1. DBA one detached a database using his/her Windows login
  2. DBA two tries to attach the database using his/her Windows login. DBA two is unable attach the database with error below. DBA two then connects to the SQL Server instance using a SQL login which has sysadmin privileges and is still unable to attach the database with the same error message.
Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB.mdf". 
Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Solution

When a database is detached using a Windows login, the detached mdf and ldf file permissions will be set to only the Windows login that detached the database. The database files permissions need to be granted explicitly to allow either the Database Engine security identifier (SID) NT SERVICE\MSSQLSERVER or the Windows login that attempts to attach the database.

When the database is reattached, the database files permissions will be set to the Database Engine SID and the individual Windows login privileges will be removed.

Below are the steps to depict this scenario on SQL Server 2016 Developer Edition. Two domain user accounts are created - SQLAdminOne and SQLAdminTwo. Both user accounts are added to the Windows server local Administrator group and granted sysadmin privileges on the SQL Server instance.

Step 1: Create TestDB database

CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB.mdf')
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf')
GO

Step 2: Check TestDB file permission for mdf and ldf

Check TestDB file permission for mdf and ldf

Step 3: SQLAdminOne detaches TestDB database

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO

Step 4: Check TestDB file permission for mdf and ldf again

The database files mdf and ldf permissions have changed and full privileges are only granted to SQLAdminOne.

Check TestDB file permission for mdf and ldf again

Step 5: SQLAdminTwo tries to attach TestDB database

When SQLAdminTwo tries to attach the database, an access denied error is encountered because of limited permissions on the database files.

USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' ),
( FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' )
 FOR ATTACH
GO

This is the error message.

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB.mdf". 
Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Step 6: Grant full permission on both TestDB mdf and ldf files

For SQLAdminTwo to be able to reattach the database, full permissions on the mdf and ldf needs to be granted to either the Database Engine SID NT SERVICE\MSSQLSERVER or the Windows login of the DBA who attempts to reattach the database. In this tip, SQLAdminTwo is granted full privileges on the database files.

Grant full permission on both TestDB mdf and ldf files

Step 7: Reattach TestDB database

SQLAdminTwo now attaches the database and the operation is successful.

USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' ),
( FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' )
 FOR ATTACH
GO

The database files permission is now reset to the Database Engine SID NT SERVICE\MSSQLSERVER. The Windows login privileges for both SQLAdminOne and SQLAdminTwo were removed.

The database files permission is now reset to the Database Engine SID NT SERVICE\MSSQLSERVER

Summary

SQL Server sets database file access permissions when a database is attached or detached using a Windows login. This behavior is not applicable when using a SQL login to attach or detach a database as the SQL login will be using the Database Engine Service SID for the operation.

SQL Server 2005 introduced trace flag 1802 which retains the database files permission after the detach operation. The trace flag is tested and still applicable with SQL Server 2016.

This behavior is not a bug, but rather by design to make sure the login attaching the database has sufficient permissions on the files and not leveraging the SQL Server service account to attach files they do not own.

This behavior is documented on Database Detach and Attach (SQL Server) "Security - File access permissions are set during a number of database operations, including detaching or attaching a database."

Next Steps


Last Updated: 2016-12-05


get scripts

next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

View all my tips
Related Resources





Comments For This Article




Thursday, November 07, 2019 - 6:17:00 PM - SIMON LIEW Back To Top (83011)

Hi everyone,

Thank you for reading my tip and I'm glad you've found it useful. Appreciate your comments and sharing your experience.


Monday, November 04, 2019 - 2:50:29 AM - Vic Back To Top (82960)

Hi.

Nice topic.

Thanks a lot!

As you wrote:  "This behavior is not applicable when using a SQL login to attach or detach a database"

Therefore for DB detach I prefer to use SQL login with SA user.

sqlcmd -S .\myinstance -Usa -Psapassowrd -Q"EXEC sp_detach_db 'MyDb', 'true';"


Wednesday, January 02, 2019 - 12:46:27 PM - Rodrigo Back To Top (78604)

Your solution is good. Thank you.

I did the same but in the folder where the files are stored, with this, all databases were able to attach.


Tuesday, January 09, 2018 - 3:34:15 PM - mbourgon Back To Top (74930)

Simon, thanks for this - was getting frustrated since I couldn't get the damn thing to attach.  Hopped on, set perms for the SQL Service user, and then the attach worked! 

 Much much appreciated.


Monday, September 11, 2017 - 1:37:35 PM - Melvyn Cruz Back To Top (66178)

 T

 Thank you It help me  a lot!!!



download





Recommended Reading

How to rename a SQL Server database

How to determine SQL Server database transaction log usage

Different ways to determine free space for SQL Server databases and database files

How to read the SQL Server Database Transaction Log

Create a Self-Service Framework for SQL Server DBA Tasks








get free sql tips
agree to terms