SQL Server – Problems when re-attaching a database that was moved to a new location

My SQL Servers typically have separate drives for data, log, & backups. Recently, we discovered that a Sharepoint server was incorrectly configured so that all the data (MDFs), log (LDFs), & backups (BAKs) files were being written to the backup drive only. So, we backed everything up, then detached all the databases and moved the data and log files to the correct drive and folder locations.

However, when we tried to reattach we got the following errors when bringing the databases back on line:

FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file ‘L:\MSSQL\Log\CM_MOSS_Config_log’. Diagnose and correct the operating system error, and retry the operation.

When the files were moved, they become owned by the mover who is a windows administrator. When the DBA tries to attach the databases, he is no longer considered an owner.

The solution to this is to grant the SQL Server service account “FULL CONTROL” to the new drive/folder location and their files.

I found a very good article on this here:

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/5598d023-db26-4da7-b0f6-c5fab968b3f9

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: