Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - The Cloud won't fix that (click for more info)
 

Access is denied error when attaching a SQL Server database


By:   |   Read Comments (2)   |   Related Tips: 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 Update:


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 T

 Thank you It help me  a lot!!!


Learn more about SQL Server tools