Introduction
Two database administrators belong to the local server Administrators group on a SQL Server machine and possess sysadmin privileges on the SQL Server instance.
Understanding MDF and LDF Files
Scenario
DBA One detaches the database with their Windows login. When DBA Two tries to attach the database with their own Windows login, they encounter an error. Subsequently, DBA two connects to the SQL Server instance with a SQL login that has sysadmin privileges but still faces the same error message when trying to attach the database.
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 someone detaches a database using a Windows login, only that login has permissions for the MDF and LDF files. To let another user attach the database, you need to grant permissions to either the Database Engine security identifier (SID) NT SERVICE\MSSQLSERVER or the Windows login trying to attach it.
Upon reattachment, the permissions for the database files will shift to the Database Engine SID, and the privileges associated with the individual Windows login will be removed.
The following steps illustrate this scenario using SQL Server 2016 Developer Edition. Two domain user accounts, SQLAdminOne and SQLAdminTwo, are created. Both accounts are added to the local Administrators group on the Windows server and are assigned sysadmin privileges on the SQL Server instance.
First Step: 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
Second Step: Verify Permissions for the MDF and LDF Files of TestDB
Third Step: SQLAdminOne detaches the TestDB database
USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'TestDB' GO
Fourth Step: Recheck Permissions for the MDF and LDF Files of TestDB
The permissions for the MDF and LDF database files have been updated, granting full access solely to SQLAdminOne.
.
Fifth Step: SQLAdminTwo Attempts to Attach the TestDB Database
When SQLAdminTwo tries to attach the database, they encounter an access denied error due to restricted 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.
Sixth Step: Grant Full Permissions on the MDF and LDF Files of TestDB
To allow SQLAdminTwo to reattach the database, full permissions must be assigned to either the Database Engine SID NT SERVICE\MSSQLSERVER or the Windows login of the DBA attempting the reattachment. In this case, SQLAdminTwo is granted full access to the database files.
Seventh Step: Reattach the TestDB database
SQLAdminTwo successfully attaches the database, completing the operation without any issues.
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 permissions for the database files have now reverted to the Database Engine SID NT SERVICE\MSSQLSERVER, and the Windows login privileges for both SQLAdminOne and SQLAdminTwo have been revoked.
Conclusion
SQL Server grants access permissions to database files when a database is attached or detached with a Windows login. It does not in the case of attaching or detaching a database by using a SQL login as it will use the SQL login's Database Engine Service SID for the operation.
SQL Server 2005 includes trace flag 1802, which keeps the database files permission after the detach. Trace flag is tested to be still in effect for SQL Server 2016.
This behavior is by design and not a bug but to ensure that any login attaching the database should have sufficient permissions on files and not rely on leveraging the SQL Server service account attach files they do not own.
