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

    Domanda

  • 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?

    mercoledì 16 aprile 2008 02:27

Risposte

  • 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!!
    mercoledì 16 aprile 2008 03:20
    Moderatore
  • 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!
    mercoledì 16 aprile 2008 15:56
    Moderatore

Tutte le risposte

  • 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!!
    mercoledì 16 aprile 2008 03:20
    Moderatore
  • 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

     

    mercoledì 16 aprile 2008 05:22
  • 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. 

     

    mercoledì 16 aprile 2008 10:11
  • 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!
    mercoledì 16 aprile 2008 15:56
    Moderatore
  • Thanks. This worked.

     

    giovedì 17 aprile 2008 00:06
  •  

    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..
    martedì 18 novembre 2008 07:13
  • 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.
    • Proposto come risposta Patibandha giovedì 10 dicembre 2009 15:44
    martedì 18 novembre 2008 08:01
    Moderatore
  • 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

    martedì 13 gennaio 2009 06:19
  • 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 :)

    lunedì 26 ottobre 2009 05:06
  • thanks man..

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


    many thanks.
    • Proposto come risposta marpaga lunedì 28 dicembre 2009 14:58
    giovedì 10 dicembre 2009 15:45
  • 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

    giovedì 28 gennaio 2010 06:20
  • 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
    • Proposto come risposta MajikTom giovedì 28 gennaio 2010 08:32
    giovedì 28 gennaio 2010 08:32
  • 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
    • Proposto come risposta GlobalRoo venerdì 23 aprile 2010 20:23
    lunedì 1 marzo 2010 23:18
  • 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.
    martedì 16 marzo 2010 15:18
  • Thanks for posting Dave, solved my problem :)
    venerdì 23 aprile 2010 20:17
  • 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. 
    sabato 19 giugno 2010 05:57
  • Thanks Mr. Sven

     

    Regards

    Tarun

    giovedì 24 giugno 2010 12:22
  • 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.

    venerdì 27 agosto 2010 23:14
  • Ok folks, hopefully this will help someone someday.

    Indeed helped, thank you!
    mercoledì 8 settembre 2010 11:20
  • It worked. Thanks a million...:)

     

    Tushar M.


    Tushar
    sabato 23 ottobre 2010 23:10
  • it works...thanks two million...dave

    • Proposto come risposta vbocan martedì 20 marzo 2012 11:32
    • Proposta come risposta annullata vbocan martedì 20 marzo 2012 11:32
    mercoledì 8 dicembre 2010 05:35
  • Thanks a million!

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

    /Lars

    • Proposto come risposta fdesigns.co.uk domenica 6 febbraio 2011 01:08
    martedì 11 gennaio 2011 20:19
  • 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

     

     

     

    domenica 6 febbraio 2011 01:12
  • "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.

     

    mercoledì 9 febbraio 2011 15:13
  • Thanks VidyaSagar!!!!
    sabato 12 febbraio 2011 23:33
  • 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
    mercoledì 16 febbraio 2011 13:30
  • 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
    sabato 12 marzo 2011 18:39
  • 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.
    giovedì 7 aprile 2011 01:02
  • 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

     

     

    venerdì 13 maggio 2011 07:08
  • 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!

    lunedì 16 maggio 2011 12:37
  • Thank you for your kindness explanation.

     

    This is useful.

    venerdì 8 luglio 2011 09:33
  • I found out that it is enough to "Right click and select 'Run as administrator'" when opening the SQL Server Management Studio.

     

    • Proposto come risposta Naomi N venerdì 30 dicembre 2011 05:24
    venerdì 9 settembre 2011 06:34
  • Excelent post,  this provided the mising information needed to resolve this error on Windows 7.

    Thanks, Cornel

    giovedì 22 settembre 2011 06:33
  • Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

    Thanx

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

    to Dave.. :)

    venerdì 11 novembre 2011 13:20

  • 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

     

    • Modificato msdn-sam martedì 29 novembre 2011 06:41 updated
    martedì 29 novembre 2011 04:14
  • 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  :)
    giovedì 22 dicembre 2011 00:05
  • 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/
    • Modificato DEGT venerdì 30 dicembre 2011 00:38
    venerdì 30 dicembre 2011 00:35
  • 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.
    sabato 21 gennaio 2012 13:27
  • Wow. Thanks a lot Mahmood. It works for me also. :D
    martedì 21 febbraio 2012 09:36
  • ...

    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!

    lunedì 27 febbraio 2012 09:37
  • Thanks!! This worked for me! banging on the wall for hours......

    Cheers,

    Pedro

    venerdì 16 marzo 2012 15:03
  • Hi,This worked for me thanks.

    lunedì 11 giugno 2012 05:25
  • Thanks.

    It works.

    martedì 10 luglio 2012 10:52
  • 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

    giovedì 19 luglio 2012 13:16
  • 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,

    venerdì 3 agosto 2012 17:58
  • Worked for me, thanks!
    venerdì 14 settembre 2012 17:27
  • 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.

    mercoledì 17 ottobre 2012 14:59
  • 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.
    mercoledì 7 novembre 2012 06:01
  • this is really a great one info. Great! Worked for me in just seconds.. after searching about 3 to 4 hours..(y)
    lunedì 19 agosto 2013 12:38
  • 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

    mercoledì 28 agosto 2013 07:37
  • 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]

    mercoledì 28 agosto 2013 07:51
    Moderatore
  • Thanks Dave! It worked for me :)

    lunedì 4 novembre 2013 08:56
  • 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.

    mercoledì 11 dicembre 2013 11:33
  • 1. Right click Sql Server Management Studio, select "run as administrator"

    2. And then try attach your database.

    giovedì 4 settembre 2014 13:10