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:
- DBA one detached a database using his/her Windows login
- 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
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.
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.
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.
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
