Access is Denied Error When Attaching a SQL Server Database – Fixed

Approved By Aswin Vijayan

Published On December 27th, 2022

Reading Time 4 Minutes Reading

Access is Denied Error When Attaching a SQL Server Database

You are reading this page because you are probably looking for a solution to Access is Denied Error when Attaching a SQL Server Database. So, in this write-up, we will sum up a resolution method to fix the problem of Below we are going to brief about preventions & causes for it.

Problem With Access is Denied Error When Attaching a SQL Server Database

There are dual database administrators who are members of SQL Server’s local administrator’s group. These members also possess sysadmin rights on server instance of SQL. There are certain scenarios that appear after Access is Denied Error, which is as follow:

1. First DBA detaches a database by his own Windows login.
2. Second DBA attaches database by his Windows login. Second DBA fails to attach DB by below error. After which, second DBA links to SQL Server instance by SQL login that possess sysadmin privileges and still does not attaches the database with this particular error message.

SOLUTION

By Windows login, if a database is detached then, detached LDF and MDF file rights will be assigned only to Windows login, which detached SQL database. SQL Server database permissions are required to let SQL Database Engine security identifier (SID) NT SERVICE\MSSQLSERVER or Windows login, which tries to attach the database. Now, database files permissions will be assigned to the SQL Database Engine SID as a database is reattached and privileges of Windows login will be eliminated.

In the stanza below, we have noted down the steps to describe the scenario for Access is Denied error when attaching a SQL Server Database in SQL Server 2016 and same too of Developer Edition. SQLAdminOne and SQLAdminTwo are the two domain user accounts that are formed or created. Both these accounts are then consolidated to Windows server local Administrator group and sysadmin privileges are granted on SQL Server instance.

STEP 1:
Create TestDB Database.

STEP 2:

For LDF and MDF files, Check permissions of TestDB file.

STEP 3:

TestDB database is detached by SQLAdminOne.

STEP 4:

Yet again, for LDF and MDF files, Check permissions of TestDB file. This changes LDF and MDF database file permissions. Further, entire privileges are given to SQLAdminOne.

STEP 5:

SQLAdminTwo attempts to attach TestDB database. When this happens, access denied when attaching SQL database error appears due to restricted permissions on files of a database.

STEP 6:

Now, both TestDB LDF and MDF files are given full permissions. In order to reattach the database SQLAdminTwo, LDF and MDF files are given full permissions to Database Engine SID NT SERVICE\MSSQLSERVER or the DBA’s Windows login who tries reattaching the database. Here, full privileges are given to SQLAdminTwo.

STEP 7:

TestDB database is to be reattached. Now, a database is attached by SQLAdminTwo and this process proves out to be totally successful.

As a result, all permissions of database files are reset to Database Engine SID NT SERVICE\MSSQLSERVER and the privileges of Windows login for SQLAdminOne as well as SQLAdminTwo SQLAdminOne and SQLAdminTwo were eliminated.

SUMMARY

File access permissions are set by SQL server when a database is detached by Windows login or when a database is attached. However, this nature is not acceptable while using SQL login for attaching or detaching a database when SQL Server Attach Database Access denied error occurred.This is because SQL login will be making use of Database Engine Service SID for this task.

A trace flag 1802 was introduced by SQL Server 2005 that preserves the permissions of database files post the detach task. After this, there is a testing of the trace flag. This is still applicable to SQL Server 2016.

Now, do not take this behavior as a bug but a design that assures the login attaching DB consists enough rights or permissions on files and not optimizing server service of SQL account in order to attach the files that do not belong to them.

The Bottom Line

In this article, we have successfully discussed the resolution steps to fix ‘Access is Denied’ error when attaching a SQL Server database. So, now whenever users encounter such an error, they know exactly what to do. We have also included relevant facts, various scenarios that crop up, and troubleshooting steps to fight the error called SQL Server Attach Database Access denied.