SQL Server Error 5120: Unable to Attach Database — Permission or Access Denied

 

Introduction

SQL Server Error 5120: Unable to open the physical file '<file_path>'. Operating system error 5: '5(Access is denied).’ occurs when attempting to attach a database but SQL Server lacks the necessary permissions to access the database files (.mdf, .ldf, or .ndf) or encounters other issues like file corruption or incorrect paths. This guide provides code-oriented solutions to diagnose and resolve Error 5120 by addressing permissions, file access, and attachment issues.

---

1. Verify File Permissions

Ensure the SQL Server service account has full control over the database files and their directory.

Code-Oriented Solution

Check the SQL Server service account:

-- View SQL Server service account
SELECT servicename, service_account
FROM sys.dm_server_services
WHERE servicename LIKE '%SQL Server%';

Action:

• Identify the service account (e.g., NT Service\MSSQLSERVER or a custom account).

• Check file permissions using PowerShell:

# Check permissions for database files
Get-Acl -Path "C:\SQLData\YourDatabase.mdf" | Format-List

• Grant full control to the SQL Server service account:

# Grant full control to SQL Server service account
$acl = Get-Acl -Path "C:\SQLData\YourDatabase.mdf"
$permission = "NT Service\MSSQLSERVER","FullControl","Allow"
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($permission)
$acl.SetAccessRule($accessRule)
Set-Acl -Path "C:\SQLData\YourDatabase.mdf" -AclObject $acl

# Repeat for .ldf and .ndf files
Set-Acl -Path "C:\SQLData\YourDatabase.ldf" -AclObject $acl

• Alternatively, use Windows Explorer:

  1. Right-click the file/folder (e.g., YourDatabase.mdf).

  2. Go to Properties > Security > Edit.

  3. Add the SQL Server service account and grant Full control.

Why? Error 5120 often occurs when the SQL Server service account lacks read/write access to the database files.

---

2. Verify File Path and Existence

Ensure the database files exist and the paths specified in the attach command are correct.

Code-Oriented Solution

Check if the files exist using PowerShell:

# Verify file existence
Test-Path -Path "C:\SQLData\YourDatabase.mdf"
Test-Path -Path "C:\SQLData\YourDatabase.ldf"

Attempt to attach the database using T-SQL:

-- Attach database
CREATE DATABASE YourDatabaseName
ON (
    FILENAME = 'C:\SQLData\YourDatabase.mdf'
),
(
    FILENAME = 'C:\SQLData\YourDatabase.ldf'
)
FOR ATTACH;

Action:

• Ensure the file paths match the actual location of the .mdf and .ldf files.

• If files were moved, update the paths in the attach command.

• If files are missing, restore them from a backup or verify the source.

Why? Incorrect or inaccessible file paths cause Error 5120.

---

3. Check SQL Server Service Account Privileges

Ensure the SQL Server service account has sufficient server-level permissions.

Code-Oriented Solution

Verify the service account’s privileges:

# Check if service account has local admin privileges (if required)
$account = "NT Service\MSSQLSERVER"  # Replace with your service account
$group = [ADSI]"WinNT://./Administrators,group"
$members = $group.Members() | ForEach-Object { $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null) }
$members -contains $account

Action:

• If the service account lacks privileges, add it to the local Administrators group (use cautiously):

# Add service account to Administrators group
Add-LocalGroupMember -Group "Administrators" -Member "NT Service\MSSQLSERVER"

• Alternatively, ensure the account has the SeManageVolumePrivilege (Perform volume maintenance tasks) via Local Security Policy:

  1. Open secpol.msc.

  2. Navigate to Local Policies > User Rights Assignment.

  3. Add the SQL Server service account to Perform volume maintenance tasks.

Why? Insufficient server-level privileges can prevent SQL Server from accessing files.

---

4. Check for File Corruption

Verify that the database files are not corrupted.

Code-Oriented Solution

Attempt to attach with verification:

-- Attach with verification
CREATE DATABASE YourDatabaseName
ON (
    FILENAME = 'C:\SQLData\YourDatabase.mdf'
),
(
    FILENAME = 'C:\SQLData\YourDatabase.ldf'
)
FOR ATTACH_REBUILD_LOG; -- Rebuilds log if corrupted

Check file integrity using DBCC:

-- Run DBCC CHECKDB after attaching (if successful)
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

Action:

• If the log file is corrupted, use FOR ATTACH_REBUILD_LOG to recreate it.

• If the .mdf file is corrupted, restore from a backup:

-- Restore database from backup
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\SQLBackups\YourDatabase.bak'
WITH REPLACE;

Why? Corrupted database files can prevent attachment, causing Error 5120.

---

5. Run SQL Server as Administrator

Temporarily run SQL Server Management Studio (SSMS) or the SQL Server service with elevated privileges to rule out permission issues.

Code-Oriented Solution

Start SSMS as Administrator:

• Right-click SSMS > Run as administrator.

Restart SQL Server service with elevated privileges:

# Stop SQL Server service
Stop-Service -Name "MSSQLSERVER"

# Start SQL Server service
Start-Service -Name "MSSQLSERVER"

Action:

• Attempt to attach the database in SSMS running as administrator:

CREATE DATABASE YourDatabaseName
ON (
    FILENAME = 'C:\SQLData\YourDatabase.mdf'
),
(
    FILENAME = 'C:\SQLData\YourDatabase.ldf'
)
FOR ATTACH;

Why? Running as administrator can bypass some permission restrictions during testing.

---

6. Test with SQLCMD or SSMS

Test the attachment process to confirm resolution.

Code-Oriented Solution

Test with sqlcmd:

# Test database attachment
sqlcmd -S sqlserverhostname -U YourLoginName -P YourPassword -Q "CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:\SQLData\YourDatabase.mdf'), (FILENAME = 'C:\SQLData\YourDatabase.ldf') FOR ATTACH"

In SSMS, run the attach command (see Step 2).

Action:

• If the error persists, verify the file paths, permissions, and service account.

• Update application code to handle attachment errors:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=master;Integrated Security=True;";
string attachQuery = @"
CREATE DATABASE YourDatabaseName
ON (FILENAME = 'C:\SQLData\YourDatabase.mdf'),
   (FILENAME = 'C:\SQLData\YourDatabase.ldf')
FOR ATTACH";

try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(attachQuery, conn)) {
            cmd.ExecuteNonQuery();
            Console.WriteLine("Database attached successfully!");
        }
    }
} catch (SqlException ex) when (ex.Number == 5120) {
    Console.WriteLine("Error 5120: Check file permissions or paths.");
} catch (Exception ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Why? Testing confirms whether the issue is resolved and isolates application-specific problems.

---

7. Analyze SQL Server Logs

Check logs for detailed information on the attachment failure.

Code-Oriented Solution

Query the error log:

-- Read error log for Error 5120
EXEC xp_readerrorlog 0, 1, N'5120', NULL, NULL, NULL, N'asc';

Action:

• Review logs for details about file access issues or other errors.

• Check the log file manually at: C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Why? Logs provide specific details, such as the exact file or permission causing the error.

---

Conclusion

SQL Server Error 5120 (Unable to attach database) can be resolved by ensuring proper file permissions, verifying file paths, checking for file corruption, and confirming the SQL Server service account’s privileges. Use the provided T-SQL and PowerShell examples to diagnose and fix the issue systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone