none
Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

    Question

  • I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL.

     

    I get the following message: Unable to open physical file "C:\ArrowSQL\Arr@Data\Arrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)".

     

    I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file.

     

    The machine runs Windows Vista Business. SQL has SP 2 loaded.

     

    What causes this?

    Wednesday, April 16, 2008 2:27 AM

Answers

  • Chris,

    Provide modify privilege for SQLService account for the folder C:\ArrowSQL\Arr@Data and then attach the db, it will attach without privilege error!!
    Wednesday, April 16, 2008 3:20 AM
  • Use the below script to find the sql service account

     

    Code Snippet

    declare @sqlser varchar(20)

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

    @value_name='objectname', @value=@sqlser OUTPUT

    PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

     

     

    After getting the service account try the below steps to provide privilege

     

    • Right click on the folder C:\ArrowSQL\Arr@Data and click on properties
    • Click on security tab
    • Click on Add button and add sql service account
    • Provide modify privilege and click ok
    • Verify both mdf and ldf have modify privilege
    • Attach the db!
    Wednesday, April 16, 2008 3:56 PM

All replies

  • Chris,

    Provide modify privilege for SQLService account for the folder C:\ArrowSQL\Arr@Data and then attach the db, it will attach without privilege error!!
    Wednesday, April 16, 2008 3:20 AM
  • I do not knwo how to "modify privilages for SQL Service account for the folder". Could you direct me to some instructions on how to do this?

     

    Thanks

     

    Wednesday, April 16, 2008 5:22 AM
  • First, check the service account of your SQL Server instance. You can get it using Configuration Manager.

    Second, give this account Modify permission for the data folder. 

     

    Wednesday, April 16, 2008 10:11 AM
  • Use the below script to find the sql service account

     

    Code Snippet

    declare @sqlser varchar(20)

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

    @value_name='objectname', @value=@sqlser OUTPUT

    PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

     

     

    After getting the service account try the below steps to provide privilege

     

    • Right click on the folder C:\ArrowSQL\Arr@Data and click on properties
    • Click on security tab
    • Click on Add button and add sql service account
    • Provide modify privilege and click ok
    • Verify both mdf and ldf have modify privilege
    • Attach the db!
    Wednesday, April 16, 2008 3:56 PM
  • Thanks. This worked.

     

    Thursday, April 17, 2008 12:06 AM
  •  

    uhmm im getting the same error and tried to execute the code snippet u gave but when i execute it , it gives me the error...

     

    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

    Msg 22001, Level 1, State 1

     

    sorry im kinda new to this stuff and i really need to fix this problem asap..
    Tuesday, November 18, 2008 7:13 AM
  • The above script is written for SQL 2005. Can you provide your SQL Version.

     

    You can also try the below alternate method to find service account

     

    • Goto RUN
    • Type services.msc & press enter
    • In service control manager find the below service w.r.t to your edition.

    For SQL 2000

    MSSQLSERVER -- for default instance

    MSSQL$instancename -- for named instance

     

    For SQL 2005 & SQL 2008

    SQL Server (MSSQLSERVER) -- for default instance

    SQL Server (instancename) -- for named instance

    • Check the "Log On as" for the SQL service and provide modify privilege to that ID in the folder.
    • Proposed as answer by Patibandha Thursday, December 10, 2009 3:44 PM
    Tuesday, November 18, 2008 8:01 AM
  • hello i'm new to sql and asp.net. i have a web application where i develop at work. which seem to hv probelms to coonecting to the database sql 2005. i have tried the above mention solutions, but fail to edit the folders and both mdf n ldf file for permission, due to the fact that when i right click the folder and click on properties, i do not see a "security tab " (i'm using windows xp sp 3). but when i took the application home and test it out the worked fine. so what did i do wrong on my office machine? Please advise. i need to solve this problem ASAp. any solution would be of great help.

    Unable to open the physical file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system error 5: "5(Access is denied.)".
    An attempt to attach an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Unable to open the physical file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system error 5: "5(Access is denied.)".
    An attempt to attach an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [SqlException (0x80131904): Unable to open the physical file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system error 5: "5(Access is denied.)".
    An attempt to attach an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735171
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
    System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
    System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
    System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
    System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
    System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
    System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
    System.Data.SqlClient.SqlConnection.Open() +111
    System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
    System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
    System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
    System.Web.UI.WebControls.ListControl.PerformSelect() +31
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
    System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
    System.Web.UI.Control.PreRenderRecursiveInternal() +77
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

    Tuesday, January 13, 2009 6:19 AM
  • Simply open "SQL Server Configuration Manager"
    In side "SQL Server Configuration Manager" in the right-side, right-click on the service name which you are using currently
    Select Properties
    Now you can do one of the followings:
         - Change the log on service account to an account with appropriate privileges.
         OR
         - Give the selected log on service account an appropriate privileges on your file system (for example: D:\SQLDatabase\)

    Good luck :)

    Monday, October 26, 2009 5:06 AM
  • thanks man..

    this work for me..
    as i m using win7 - sql 2005  express..


    many thanks.
    • Proposed as answer by marpaga Monday, December 28, 2009 2:58 PM
    Thursday, December 10, 2009 3:45 PM
  • OS: Win7 sp1 (fully patched to Jan 25th, 2010)
    Office2007 with integrated BCM (fully patched to Jan 25th, 2010)
    Auto-installed SQL Server2005 Express (fully patched to Jan 25th, 2010)
    Created BCM database called SmallBusiness

    I am trying to move BCM database from the default C:\Users\User_name\AppData\Local\Microsoft\Business Contact Manager folder.

    - First I backed up the db and log.
    - I copied the files into D:\Databases
    - Gave permission on the two files to the Log On for SQL Server Service
       - I also gave permission to SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ  <- Most people forget that, and I haven't seen anyone mention it, but it's on the default db BCM creates)
    - I detached the SmallBusiness Database
    - I try to attach the new file:
    USE [master];
    GO
    CREATE DATABASE [SmallBusiness]
    ON
    ( FILENAME = N'D:\Databases\SmallBusiness.mdf'
    ),
    ( FILENAME
    = N'D:\Databases\SmallBusiness.ldf'
    )
    FOR
    ATTACH
    GO
    - I get the infamous "SQL Server Database Error: Unable to open the physical file "D:\Databases\SmallBusiness.mdf". Operating system error 5: "5(error not found)"

    So after reading many posts on this topic, and trying a few things, I decide in desperation to Share the entire drive to everyone, every logon UID on the entire system with modify all. No change.

    Ok, so now this is getting silly.

    - I create a brand new dummy database using Toad for Data Analysis' Wizard in D:\Databases called TestDB.mdf
    - I create a schema and a few tables and fill them up with junk.
    - I switch users and everything works as expected.

    Now for the kicker:

    - I detach this new DB which created successfully in D:\Databases.
    - I try to re-attach it from the same location and guess what?
    "SQL Server Database Error: Unable to open the physical file "D:\Databases\TestDB.mdf". Operating system error 5: "5(error not found)"

    Ok, so now this is beyond silly.

    So I try to re-attach the original BCM DB files back as they were to restore everything the way it was.
    "SQL Server Database Error: Unable to open the physical file "C:\Users\User_name\AppData\Local\Microsoft\Business Contact Manager\SmallBusiness.mdf". Operating system error 5: "5(error not found)"

    I am Admin, I installed everything, I own the DB, the file permissions on the original C:\ DB did not change.

    This is not good.

    Can anyone at MS suggest anything?

    Regards,
    Tom

    Thursday, January 28, 2010 6:20 AM
  • Ok folks, hopefully this will help someone someday.

    Even if YOU create a DB or if BCM created one on your behalf, once you detach it, you actually have to explicitly grant yourself full control over the mdf and ldf, even if you are part of the Administrators group in order to re-attach it.

    I think this is a WIN7 bug.

    The files were granted full control to Administrators, the SQLServer2005 Service Logon, and the SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    But if you want to re-attach it with WIN7, you have to explicitely grant yourself full control. (why? ...who knows...)

    So, to move a WIN7 Office2007 BCM database from it's default location to anywhere on the planet and to ensure any database attach activity doesn't give you the "unknown error #5":

    1) backup your files
    2) copy your mdf and ldf to your favorite folder
    3) ensure at minimum the following UIDs have full control over them
    - SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    - Your current Administrator UserID (even if Administrators have full control and you are an Administrator, you still have to add yourself)
    - SQLServer2005 Service Logon
      (you can get this on WIN7 thusly:

    declare

     

     

     

     

     

     

     
    @sqlser varchar(20)
    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQL$MSSMLBIZ', @value_name='objectname', @value=@sqlser OUTPUT
    PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

    3) detach your DB

    USE [master];
    GO
    EXEC sp_detach_db @dbname = N'SmallBusiness', @skipchecks = 'true', @keepfulltextindexfile='true'
    GO

    4) attach the DB from the new location (ensure you own it while you're at it)

    USE
    [master];
    GO
    CREATE DATABASE [SmallBusiness] ON
    ( FILENAME = N'D:\Databases\SmallBusiness.mdf' ),
    ( FILENAME
    = N'D:\Databases\SmallBusiness.ldf' )
    FOR ATTACH
    GO
    if exists (select name from master.sys.databases sd where name = N'SmallBusiness' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AnethSmallBusiness].dbo.sp_changedbowner @loginame=N'MACHINE_NAME\USERNAME', @map=false
    GO

    5) enjoy

    I tried moving to a completely different computer \\COMPUTERNAME2\Databases\SmallBusiness.mdf
    works perfectly.

    PS> My setup is brand new, but sqlcmd doesn't work. Don't waste time, get Toad for Data Analysis. You can do anything on ANY DB with this thing. Great scripting tool, and it has a GUI for all you lazy folk...

    Regards,
    Tom
    • Proposed as answer by MajikTom Thursday, January 28, 2010 8:32 AM
    Thursday, January 28, 2010 8:32 AM
  • I agree with Majiktom, it may seem like permissions are OK but you still get errors.  I feel the process is confusing, to say the least! 

    My quick fix was to:
    - right-click on the file in Windows Explorer, select Properties
    - select the Security tab
    - Click Advanced
    - Click Change Permission
    - Uncheck "Include inheritable permissions...", a window will open
    - Click Remove (removes all permissions), the window will close
    - Click Add
    - Enter your login name and click OK, the permission window will open
    - Check Full Control - Allow
    - Click OK, OK, OK, OK

    Do this for the MDF and LDF files.

    I was then able to attach the database.

    Cheers,

    -dave
    • Proposed as answer by GlobalRoo Friday, April 23, 2010 8:23 PM
    Monday, March 01, 2010 11:18 PM
  • Hi,

    I have failed some of backup jobs with below error message. I don't know how I can fix this problem. Could you give me some advice?

    Executed as user: COMP\XXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:07:39 AM  Progress: 2010-03-16 11:07:41.99     Source: {91EBDCB5-4E9D-4945-ABA7-57BB23DA6176}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2010-03-16 11:07:43.46     Code: 0xC002F210     Source: Backup DB Execute SQL Task     Description: Executing the query "BACKUP DATABASE [TO1] TO  DISK = N'\\server5\mitsd..." failed with the following error: "Cannot open backup device '\\server5\mitsdb_backup$\TRG\XXXXX\TO1\TO1.bak'. Operating system error 5(Access is denied.).  BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:07:39 AM  Finished: 11:07:43 AM  Elapsed:  4.313 seconds.  The package execution failed.  The step failed.
    Tuesday, March 16, 2010 3:18 PM
  • Thanks for posting Dave, solved my problem :)
    Friday, April 23, 2010 8:17 PM
  • Thanks Tom and Dave, I was pulling my hair out messing with SQL Server network service account permissions, but indeed it did seem to be that after reinstalling Windows I had to give myself full permissions on the .mdf and .ldf files.  Very odd.  I claim this is a SQL server bug, because even when I let SQL server run as administrator it didn't fix it, even though it gave it the permission to do it. 
    Saturday, June 19, 2010 5:57 AM
  • Thanks Mr. Sven

     

    Regards

    Tarun

    Thursday, June 24, 2010 12:22 PM
  • Yes, it seems that I had to right click on the folder containing the .MDF and .LDF files and add my current logged in user "explicit" permissions to the directory in order for SQL server to attach the DB.

    This is a fact EVEN THOUGH  my current logged in user is in the "(local)/Administrators"  group and the "(local)/Administrators" group already has full file control permissions of that directory.   Also, my current logged in user is a DB "admin" for the SQL server.   For some reason I needed to "explicitly" add my currently logged in user accounts name.

    NOTE: I need to point out that previous posters on this thread are all incorrect when they suggest that the solution is in giving the  "SQL service account name" the permissions.

    Friday, August 27, 2010 11:14 PM
  • Ok folks, hopefully this will help someone someday.

    Indeed helped, thank you!
    Wednesday, September 08, 2010 11:20 AM
  • It worked. Thanks a million...:)

     

    Tushar M.


    Tushar
    Saturday, October 23, 2010 11:10 PM
  • it works...thanks two million...dave

    • Proposed as answer by vbocan Tuesday, March 20, 2012 11:32 AM
    • Unproposed as answer by vbocan Tuesday, March 20, 2012 11:32 AM
    Wednesday, December 08, 2010 5:35 AM
  • Thanks a million!

    This really helped. Sure looks like a win7 bug to me.

    /Lars

    • Proposed as answer by fdesigns.co.uk Sunday, February 06, 2011 1:08 AM
    Tuesday, January 11, 2011 8:19 PM
  • Hi

     

    I had exactly same error but in my case, it was user permission error,

    for instance, in SQL 2008, i had an user such as 'dev\admin'

    but where the files (mdf, log) were stored, ie:c:\sql\mydb.mdf...     , there were no such user for read/write on folder 'c:\sql', so i had to add dev\admin for full control.

    then attached the db again and worked.

     

    hope that helps someone...

     

    thanks

     

     

     

    Sunday, February 06, 2011 1:12 AM
  • "The files were granted full control to Administrators, the SQLServer2005 Service Logon, and the SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    But if you want to re-attach it with WIN7, you have to explicitely grant yourself full control. (why? ...who knows...)"

    Thanks, this worked for me, granting my account Full Control too, along with the Service Account.

     

    Wednesday, February 09, 2011 3:13 PM
  • Thanks VidyaSagar!!!!
    Saturday, February 12, 2011 11:33 PM
  • Thank you Dave... That really worked!!! I had to change the permission INDIVIDUALLY for each .mdf and .ldf file.For each of these two files, I gave FULL CONTROL to USERS group and it worked.

     

    Thank you.

     

     


    Abhishek
    Wednesday, February 16, 2011 1:30 PM
  • Ok folks, hopefully this will help someone someday.

    Even if YOU create a DB or if BCM created one on your behalf, once you detach it, you actually have to explicitly grant yourself full control over the mdf and ldf, even if you are part of the Administrators group in order to re-attach it.

    I think this is a WIN7 bug.

    The files were granted full control to Administrators, the SQLServer2005 Service Logon, and the SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    But if you want to re-attach it with WIN7, you have to explicitely grant yourself full control. (why? ...who knows...)

    So, to move a WIN7 Office2007 BCM database from it's default location to anywhere on the planet and to ensure any database attach activity doesn't give you the "unknown error #5":

    1) backup your files
    2) copy your mdf and ldf to your favorite folder
    3) ensure at minimum the following UIDs have full control over them
    - SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    - Your current Administrator UserID (even if Administrators have full control and you are an Administrator, you still have to add yourself)
    - SQLServer2005 Service Logon
      (you can get this on WIN7 thusly:

    declare

     

     

     

     

     

     

     
    @sqlser varchar(20)
    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQL$MSSMLBIZ', @value_name='objectname', @value=@sqlser OUTPUT
    PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

    3) detach your DB

    USE [master];
    GO
    EXEC sp_detach_db @dbname = N'SmallBusiness', @skipchecks = 'true', @keepfulltextindexfile='true'
    GO

    4) attach the DB from the new location (ensure you own it while you're at it)

    USE
    [master];
    GO
    CREATE DATABASE [SmallBusiness] ON
    ( FILENAME = N'D:\Databases\SmallBusiness.mdf' ),
    ( FILENAME
    = N'D:\Databases\SmallBusiness.ldf' )
    FOR ATTACH
    GO
    if exists (select name from master.sys.databases sd where name = N'SmallBusiness' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AnethSmallBusiness].dbo.sp_changedbowner @loginame=N'MACHINE_NAME\USERNAME', @map=false
    GO

    5) enjoy

    I tried moving to a completely different computer \\COMPUTERNAME2\Databases\SmallBusiness.mdf
    works perfectly.

    PS> My setup is brand new, but sqlcmd doesn't work. Don't waste time, get Toad for Data Analysis. You can do anything on ANY DB with this thing. Great scripting tool, and it has a GUI for all you lazy folk...

    Regards,
    Tom
    this actually worked, thanks
    Saturday, March 12, 2011 6:39 PM
  • Thank you!  I was getting the same "Unable to open physical file..." message while trying to attach a database.  Giving the appropriate users "Full Control" over the folder containing the mdf files solved the problem.  I am using Windows 7 x64 and SQL Server 2005 Express.
    Thursday, April 07, 2011 1:02 AM
  • Regarding the 5(error not found), It is the problem of permission.

    You first allow permission to every users....
    1. Go to your db path (.mdf and ldf)
    2. set permission to full control to every role ie. System, Admin, Users.

    This will help to run your query.

    Thanks
    Amit Malviya

     

     

    Friday, May 13, 2011 7:08 AM
  • Mr.Blaze4fun,

    That was Ace!! I could open my SQL server 2005 database, by doing the above modifications to both mdf,ldf files and doing 1 simple extra step. Once the attaching is done, right click the database--> properties-->files--> specify the owner(if it is blank).


    Cheers!

    Monday, May 16, 2011 12:37 PM
  • Thank you for your kindness explanation.

     

    This is useful.

    Friday, July 08, 2011 9:33 AM
  • I found out that it is enough to "Right click and select 'Run as administrator'" when opening the SQL Server Management Studio.

     

    • Proposed as answer by Naomi N Friday, December 30, 2011 5:24 AM
    Friday, September 09, 2011 6:34 AM
  • Excelent post,  this provided the mising information needed to resolve this error on Windows 7.

    Thanks, Cornel

    Thursday, September 22, 2011 6:33 AM
  • Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    ...............

    to Dave.. :)

    Friday, November 11, 2011 1:20 PM

  • I agree with Majiktom, it may seem like permissions are OK but you still get errors.  I feel the process is confusing, to say the least! 

    My quick fix was to:
    - right-click on the file in Windows Explorer, select Properties
    - select the Security tab
    - Click Advanced
    - Click Change Permission
    - Uncheck "Include inheritable permissions...", a window will open
    - Click Remove (removes all permissions), the window will close
    - Click Add
    - Enter your login name and click OK, the permission window will open
    - Check Full Control - Allow
    - Click OK, OK, OK, OK

    Do this for the MDF and LDF files.

    I was then able to attach the database.

    Cheers,

    -dave

    This Works like a charm, I added all my DBs ( some were from sql server2000 even)..

    Thanks

     

    Best Regards,

    HeSaDi 

    www.e-nowave.com

     

    • Edited by msdn-sam Tuesday, November 29, 2011 6:41 AM updated
    Tuesday, November 29, 2011 4:14 AM
  • Use the below script to find the sql service account

      

    Code Snippet

    declare @sqlser varchar(20)

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

    @value_name='objectname', @value=@sqlser OUTPUT

    PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

     

     

     

    After getting the service account try the below steps to provide privilege

      

    • Right click on the folder C:\ArrowSQL\Arr@Data and click on properties
    • Click on security tab
    • Click on Add button and add sql service account
    • Provide modify privilege and click ok
    • Verify both mdf and ldf have modify privilege
    • Attach the db!
    it Works !!!....Thanks a lot  :)
    Thursday, December 22, 2011 12:05 AM
  • Actually I think most people fail to be clear enough on their instructions. Someone here indicated the culprit was that oneself was not in the list of allowed users and that was the only necessary permission change.

    That is not true. In my setup I moved the database from the default C:\xxx location to D:\Databases\{database_name}\Data as I also have a D:\Databases\{database_name}\Backup directory. The MDF and LDF files reside in that directory (Data). I am using SQL Server 2008 R2 Express under Windows 7 Home Premium 64 bits.

    The registry and config mgr indicated the user as "NT AUTHORITY\NetworkService" but that was NOT recognized by the Windows permissions dialog, instead you have to use "Network Service" for it to be recognized as a proper account. I added Full Control permissions to "Network Service" and myself (a restricted account) to the d:\Databases\{database_name} directory but it still failed to bring the DB online.

    I checked that the Data directory had inherited those permissions but same problem. You would expect the FILES in those directories INHERIT those permissions, apparently NOT! When I checked the permissions on the MDF and LDF files in the Data directory, neither had the Network Service or my user account listed! Since I don't believe in opening up everything, I proceeded cautiously.

    Now I added the "Network Service" with Full Control to the MDF file alone (on the file itself, not the directory). The attempt failed again, only this time I indicated the error was now ONLY on the LDF file (remember I didn't give it permissions?). So, by granting "Network Service" Full Control over both the MDF and LDF files *explicitely* I was able to bring the database online using SQL Management Studio on my restricted account.

    I did not need to add my user account to the permissions list of the MDF and LDF files!, the Network Service (under which SQL Server operates) was enough. I hope that helps someone.


    http://www.virtual-aviation.net/
    • Edited by DEGT Friday, December 30, 2011 12:38 AM
    Friday, December 30, 2011 12:35 AM
  • One thing that I did that worked was to use the "Run as Administrator" option on the Sql Server Management Studio.   When I did that, I didn't get this error any longer.
    Saturday, January 21, 2012 1:27 PM
  • Wow. Thanks a lot Mahmood. It works for me also. :D
    Tuesday, February 21, 2012 9:36 AM
  • ...

    So, to move a WIN7 Office2007 BCM database from it's default location to anywhere on the planet and to ensure any database attach activity doesn't give you the "unknown error #5":

    1) backup your files
    2) copy your mdf and ldf to your favorite folder
    3) ensure at minimum the following UIDs have full control over them
    - SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
    - Your current Administrator UserID (even if Administrators have full control and you are an Administrator, you still have to add yourself)
    - SQLServer2005 Service Logon

    ...

    Yes, this is it, the solution to this weird problem. Thanks!

    Monday, February 27, 2012 9:37 AM
  • Thanks!! This worked for me! banging on the wall for hours......

    Cheers,

    Pedro

    Friday, March 16, 2012 3:03 PM
  • Hi,This worked for me thanks.

    Monday, June 11, 2012 5:25 AM
  • Thanks.

    It works.

    Tuesday, July 10, 2012 10:52 AM
  • I'm confirming that you can Attach the database only after the user under which you want to attach the db has Full NTFS permissions over the file. Doesn't matter if you're member of local admins or other administrative groups. I had Win 7 sp1 fully patched with SQL Express 2012.

    RR IT Professional

    Thursday, July 19, 2012 1:16 PM
  • thanks guys!!!!

    i am using SQL2012 Entp. with win7 64bit  its works for me.

    on the other hand , you may choose the (local system) service accounts while installation or from the configuration manager.

    regards,

    Friday, August 03, 2012 5:58 PM
  • Worked for me, thanks!
    Friday, September 14, 2012 5:27 PM
  • I had this issue and all the solutions online was kind of misleading to my issue. I have the solution here.

    http://dotnet-programming-solutions.blogspot.com/2012/10/attach-database-encountered-operating.html

    The solution was to Run SSMS as Administrator.

    Wednesday, October 17, 2012 2:59 PM
  • Chris,

    Provide modify privilege for SQLService account for the folder C:\ArrowSQL\Arr@Data and then attach the db, it will attach without privilege error!!

    this also help me. thanks.
    Wednesday, November 07, 2012 6:01 AM
  • this is really a great one info. Great! Worked for me in just seconds.. after searching about 3 to 4 hours..(y)
    Monday, August 19, 2013 12:38 PM
  • put the database file in separate folder

    1-R click on folder

    2-select properties

    3-go to the security tab

    4-click on edit button

    5-click Add

    6-click Advanced

    7-click Find Now

    8-select "Everyone" in name column >> ok >> ok

    9-select "Everyone" form group or user name

    10-checked the full control >> ok >> ok

    now you able to attach database file in SSMS

    tested in windows 7 and 8 under SQL server 2008 and 2012

    Wednesday, August 28, 2013 7:37 AM
  • 8-select "Everyone" in name column >> ok >> ok


    Permissions for "Everyone" is a security issue; you should avoid this. Give permissions only to the service account of SQL Server, not more.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 28, 2013 7:51 AM
  • Thanks Dave! It worked for me :)

    Monday, November 04, 2013 8:56 AM
  • Thank You Blaze4Fun,

    I needed to grant myself modify permission using your instructions. There's an armada of noobs like us wanting to learn SQL Server, whether for personal interest, or to supplement simple routine tasks at work. I can assume many download Adventureworks and could then be put off by error messages that result in complicated resolutions on forums.

    Your answer is clear, concise and importantly your fix uses the simplest method and most popular interface (To Date) which is 'Windows 7.' I'm sure your answer has helped more then those who didn't have accounts to up vote.

    Wednesday, December 11, 2013 11:33 AM
  • 1. Right click Sql Server Management Studio, select "run as administrator"

    2. And then try attach your database.

    Thursday, September 04, 2014 1:10 PM