none
Problem attaching a database

    Question

  •     Hello, I have made a database in SQL Server 2005 Developer edition. I tried to attach it to a SQL Server 2005 Express Edition but I coundn't. I can attach and dettach it to the developer edition but I cannot do it to the express edition. When I am running the store procedure:

    exec sys.sp_attach_single_file_db @dbname = 'LinGS',   @physname = 'c:\LinGS.mdf'

      I receive the following error:

    File activation failure. The physical file name "C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS_log.ldf" may be incorrect.
    .Net SqlClient Data Provider: Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'c:\LinGS_log.LDF'.
    .Net SqlClient Data Provider: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'LinGS'. CREATE DATABASE is aborted.

    Can someone help me how to attach this database?

      Thanks in advance

    ps. Now I cannot attach to the developer edition by running the following command:

    exec sys.sp_attach_single_file_db @dbname = 'LinGS',
       @physname = 'C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS.mdf'

    I receive the following error:

    .Net SqlClient Data Provider: Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file "C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS.mdf". Operating system error 5: "5(error not found)".

    Sunday, October 30, 2005 6:04 AM

Answers

  • The error 5 is an "access denied" error.
    You may need to use an admin account to actually change the security on the file such that the sql server process can open it.
    Hope that helps.
    Tuesday, November 01, 2005 9:29 PM

All replies

  • The error 5 is an "access denied" error.
    You may need to use an admin account to actually change the security on the file such that the sql server process can open it.
    Hope that helps.
    Tuesday, November 01, 2005 9:29 PM
  • An alternative solution is to not use the root directory as the destination.
    Wednesday, November 02, 2005 10:56 PM
  • I am having the same problem when trying to attach a database. I can attach the database just fine from the computer on which SQL Server 2005 is installed. However, if I try to attach the database from a workstation I receive the error "Unable to open the physical file 'c:\Program Files\Microsoft SQL Server\MSSQL1.1\MSSQL\Data\TravCom.mdf'. Operating system error 5 (Access Denied)."

    Previously I was using SQL Server 2000 and this worked just fine from both the server and the workstation. However, since upgrading to SQL Server 2005 I no longer can attach from the workstation. I can detach the database from the workstation, but I cannot reattach it.

    Just for kicks and giggles I enabled complete sharing to the Data folder on the server. However, that shouldn't matter. It didn't in previous versions.

    Also, I can create a brand new database on the server from a workstation as well as any other administrative functions I can think of, just not attaching a database. I tried both the sp_attach_db stored procedure as well as CREATE DATABASE FOR ATTACH command. Both give me the same error.

    Does anyone have a solution - or could this be a bug? I'm thinking the latter.
    Tuesday, November 15, 2005 11:45 PM
  • If you are getting "Operating system error 5 (Access Denied)", then this is strictly a Windows security issue. Are you sure that the account SQL Server runs in has access to the file? Does it work if you start SQL Server in your account by switching to the MSSQL\BINN directory and starting sqlservr.exe from the cmd window?
    Wednesday, November 16, 2005 5:14 PM
  • I'm not sure I understand how this can be a Windows security issue since 1) it worked just fine before under SQL Server 7 and SQL Server 2000, and 2) it works just fine when trying to attach from the server itself. Let me try to explain further.

    I have created a utility which allows a user to attach a database. The utility creates an ODBC connection to the SQL Server using the sa account and password. The utility then calls the CREATE DATABASE xxx FOR ATTACH. This utility works splendidly when run from the same computer that SQL Server is installed. However, when run from a workstation it fails with the Access Denied error.

    Since the utility works when run from the server then I would imagine that the account SQL Server is running under has access to the file. It shouldn't matter which computer is issuing the CREATE DATABASE command, as long as that workstation has access to the server and the connection is made with the proper credentials (sa account). Furthermore, I am able to run many other statements from the utility on the workstation, including detaching the database, creating a new database, shrinking the log file, create users, grant/revoke priveledges, and every other administrative task I have tried. Just not the attach. I even tried using the classic sp_attach_db stored procedure to no avail.

    So, could this still be a security issue? The only thing I can really think of is that the way the attach in SQL 2005 has changed where it must be using the Windows account from the workstation somehow to validate access to the file.

    Any other suggestions?
    Wednesday, November 16, 2005 6:50 PM
  • Hi there,

    You've probably resolved this issue but here's my 2cents worth:

    I found that when using the Attach Database wizard i've had to remove the LDF entry where there wasn't an LDF file. The wizard create one for you automatically.

    So if, in your script, you're specifying an LDF file, maybe try removing it.

    Let me know if it works.

     

    Cheers,

     

     

     

    Tuesday, February 21, 2006 11:40 PM
  • I am getting the same issue! and I believe it is a system security issue...

    so... how do you get SQL Server to run under the system administrator account.. I get this error when I try "Attaching" or "Createing" a whole new database from a script... the same error "System Error 5 (Accessed Denied)"

    I need to change ALL our SQL Server instances to run under the administrator account... how do we do this?

     

    ward0093

    Tuesday, March 14, 2006 3:42 PM
  • I had the same problem and changed Account that was used by SQL Server service to log on. See appropriate SQL Server service proterties in "SQL Server Configuration Manager -> SQL Server Services"
    Friday, April 28, 2006 1:09 PM
  • I had the same problem.

    Solution: Install SQL server express with SQLACCOUNT paremeter

    Example:

    setup.exe /qn INSTANCENAME=MYSERVER ADDLOCAL=SQL_Engine,SQL_Data_Files,Client_Components,Connectivity SQLBROWSERAUTOSTART=1 SQLACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" AGTACCOUNT="NT AUTHORITY\SYSTEM" ASACCOUNT="NT AUTHORITY\SYSTEM" RSACCOUNT="NT AUTHORITY\SYSTEM"

    Default is Network Service (NT AUTHORITY\NetworkService) Account.

    Friday, May 12, 2006 10:12 AM
  • If you installed it already (with the NetworkService account) is there a way to change the existing install?  I don't want to have to go un-install all the machines running with NetworkService.

    Thanks,

    ward0093

    Thursday, May 18, 2006 7:38 PM
  • If you detach a database that is your default database, you can encounter an error when trying to re-attach it. Try connecting to the database under another administrator account, set your default database to master, reconnect under your original account, and try to attach the database.

    Wednesday, June 07, 2006 4:21 AM
  • does your problem solved?

    in case it doesn't,

    the first time u connect to the SQL server, try using Windows Authentication instead of SQL Server Authentication.

    i face the same problem and it works that way.

    Wednesday, June 21, 2006 2:47 PM
  • Hi!
    Well I faced the same problem creating a new database through scripts. Pazout's comment helped quite a bit. There are few other important things that I noticed though:
     I was able to create a database in "C:" (i.e.\myname\Database.mdf" ) or any other drive. But the security error occured when I tried to create the database files in "Program Files".

    Now to resolve this you need not to uninstall Sql Express. Just go to SQL express configuration manager and click on Services from the left pane. From the right pane select Sql server service and go to its properties. The builtin login should be "Nerwork Service", change it to "Local system" and restart the service. thats it.

    cheers :)
    • Proposed as answer by ceez38 Thursday, April 29, 2010 5:36 PM
    Friday, November 24, 2006 10:50 AM
  • Is this problem solved? The easy way:
    For your data_file.mdf diretory, make sure the user security group has write and create data permission.
    Sunday, February 25, 2007 8:17 AM
  • Hello.

    I fixed my problem attaching a database, reading just the last post :-) Thanks!

    Wednesday, February 28, 2007 9:48 AM
  • Hello, I had also the same problem with detaching and re-attaching a SQL-2005-Database.

    I detected, that the windows-user, who was the owner of the database, was the only user, who was also assgined in the NTFS-security on file-level.

    First, I checked always on folder-level, where all security-definitions were ok.

    But the database-files itself hat only one windows-user assgined.

    After detach of the database, you have problems to logon again to the SQL-database, because the default-database was detached before.

    So when you try to logon with another windows-administrator, you run into this problem.

    With SQL-2000, we never had this problem.

     

    Best regards,

    Robert Ganter

     

    Tuesday, March 13, 2007 1:07 PM
  • Hi,

    My problem was also solved by attaching the database directly on the server. It failed when I tried it on a client machine.

    Thanks for the help in this thread guys!

     

    Tom

    Thursday, March 15, 2007 9:51 AM
  • Hi,

    I have a similar problem. I have to install my application and then install SQL 2005 Express in SQL authentication mode. I have made a setup file which will call my application and once it installed it would call Express installation file. Now the problem is SQL will not allow attaching database from c:/program files/DB folder cos service shows "network service" after installation. If that is "Local System" then databases are attached.

     

    Does anyone know command prompt instruction to set the SQL service to "Local System" in normal mode and not in Silent mode?

    Friday, June 22, 2007 12:39 PM
  • Dear message board,

     

    I thought I might post this in the hope that someone if in a similar situation would be able to get out of it.

    I have SQL server 2005 remotely looking at a SQL Server 2000 tree (on the most part it works ok), appart (as it seems) attaching databases (sometimes!).

     

    I got this error..

     

    Code Snippet

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\{THE DIRECTORY TREE}\{THE FILE}.MDF'. (Microsoft SQL Server, Error: 5123)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0534&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

     

     

    *Obviously I have marked out the directory and file with {THE DIRECTORY TREE} and {THE FILE} accordingly.

     

    Solution (in query analyser on the SQL server 2000 Enterprise Manager):

     

    Code Snippet
    EXEC sp_detach_db @dbname = '{OLD NAME OF DATABASE}'
    EXEC sp_attach_single_file_db @dbname = '{OLD NAME OF DATABASE}',
    @physname ='
    {THE DIRECTORY TREE}\{THE FILE}.MDF'

     

     

     

    *Obviously I have marked out the directory and file with {THE DIRECTORY TREE} and {THE FILE} accordingly, and the {OLD NAME OF DATABASE}.

     

    Seemed to work!

     

    Hope this helps someone. Smile

     

     

    Friday, August 24, 2007 3:56 PM
  • Login as Administrator.  Right click on Databases.  Click on Attach then go to the location where the database is and click on the .mdf file.  This will bring in both th .mdf file and the .ldf file.

     

    Friday, October 12, 2007 3:42 PM
  • Hi Dick... your advise can fixed my problem with ERROR 5172..

     

    Thanx Dick..!

     

     

    Friday, January 04, 2008 7:12 PM
  • Never knew error 5 is an "access denied" error Sad until I saw this comments! Thanks a lot!

    I logged on with the 'sa' account that got this error (using 'sa' account can attach the AdventureWorksLT_Data.mdf but AdventureWorks_Data.mdf). When I logged back on with 'Windows Authentication', I was able to successfully attach
    AdventureWorks_Data.mdf.
    Thursday, April 17, 2008 7:33 PM
  • I realize this is an old post, but I found something interesting that might help future googlers.  Detaching a database from SQL 2005 results in security permissions for the file being wiped out for all users except the one I used to detach the file (in my case a windows sys admin user).  This includes the local system account!  When I added back SYSTEM, I could attach no problem.  Why would detaching wipe out security permissions for the file!?

     

    Thursday, June 05, 2008 7:46 PM
  • This worked like magic, thanx bro..

    Saturday, June 21, 2008 1:01 PM
  • Hi!
    I have exactly the same problem. I can easily detach a database but not attach. The issue appears because we need this tasks to be performed remotely. For that, we had create a web service that we had deploy on IIS 5.1. But we think this can't be an IIS issue, because we can succesufly perform other tasks under the webservice like file copies and database detaches. The only thing that doesn't work is the attach.
    We are trying everything we can remember in order to fix this problem, but until now we didn't figure out what is happening. And we need this to be solved quickly!!!

    I have read some suggestions posted here.

     momibutt wrote:
    Hi!
    [...]
    Now to resolve this you need not to uninstall Sql Express. Just go to SQL express configuration manager and click on Services from the left pane. From the right pane select Sql server service and go to its properties. The builtin login should be "Nerwork Service", change it to "Local system" and restart the service.
    [...]

    I tried to do this and I got a error with a message similar to: It's not possible to find the object or property [0x80092004]. So for now it wasn't a solution.


     LoRez wrote:

    [...]
    Detaching a database from SQL 2005 results in security permissions for the file being wiped out for all users except the one I used to detach the file (in my case a windows sys admin user).  This includes the local system account!  When I added back SYSTEM, I could attach no problem.  Why would detaching wipe out security permissions for the file!?

    What that's mean? I don't understand what you mean with "added back SYSTEM".


    Any help would be huge appreciate! After lots of hours trying to figure out what is happening, we are almost getting crazy!!!
    Thanks in advance,
    Tânia

    Notice that we are running under an administrator account and that we have set a password for sa user under sql.
    Monday, July 07, 2008 5:34 PM
  •  

    It's possible that the user you are using to detach the database is not the same user you are using to attach it.  This is often because the SQL Server runs under a specific user's credentials which are not the same as those used to detach the file.  This could be the local system account for instance (i.e. SYSTEM on the "Security" tab of the file's properties) or the NETWORK SERVICE user.   

     

    If you used ADO.NET to detach, my experience is that only the security settings of the user on the connection string will be preserved.  The security settings for your SQL Server service user (and any other user you might be using to reattach) will be wiped out.  Perhaps, it is happening with other connection mechanisms like DMO or SMO.  I can't remember or haven't tried them out.

     

    To verify this is your problem, take a look at the security settings for a database file that is currently failing to attach (under the file properties).  If you only see security for one user (the one that detached the file), then you've found your problem.

     

    To work around the problem, you might try using the same user to attach and detach the database, or you could alter your process to add the security settings back to the file.  I don't have a ready automated solution for you to do the second alternative, but it should be possible to do something with WMI for instance.

     

    Good luck

    Monday, July 07, 2008 8:15 PM
  • Hi!
    This is what we are doing to attach a database into sql server 2005, where:

    we had define a password to the sa user
    nomeBd is the database name to be attached
    ficheiros is a string in the following format [C:\data\127.mdf],[C:\data\127.ldf]


    Code Snippet

    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
       try
       {
          srv.LoginSecure = false;
          srv.Connect(nomeServer, "sa", "password");
          //attach

          srv.AttachDB(nomeBd, ficheiros);
          srv.ExecuteImmediate("alter database " + nomeBd + " SET ONLINE WITH ROLLBACK IMMEDIATE", SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, null);

          return true;
       }
       catch (SqlException)
       {
          return false;
       }
       finally
       {
          if (srv != null)
          {
             srv.DisConnect();
             //srv = null;
          }
       }


    However, at the line srv.AttachDB(nomeBd, ficheiros); we get the following message error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to open the physical file "C:\data\127.mdf". Operating system error 5: "5(O acesso é negado.)".

    where:
    C:\data\ is the database folder
    127.mdf the database to attach
    "O acesso é negado" means "Access denied"

    We are using
    srv.LoginSecure = false;
    srv.Connect(nomeServer, "sa", "password");
    because we need the attach to be performed remotely (like the detach, at this point we also have problems with the detach), but I get this error also on the local machine.

    If I change to
    srv.LoginSecure = true;
    srv.Connect(nomeServer, "", ");
    I can do the task on the local machine, but I can't connect in the others machines.

    Any idea?
    Tânia
    Tuesday, July 08, 2008 4:29 PM
  • I went to Sql Server Configuration Manager.
    On Sql server 2005 Services -> my Sql Server instance -> properties -> "log on" tab -> checked "This account" -> browse on the existing accounts and I had choose an administrator account
    I've made stop and start the service.

    With this, most of the times the detach and attach works, but sometimes it doesn't.
    Since I'm using a webservice (deployed on IIS 5.1), it seems to me that almost each time that I perform a task, I loose the permissions (because after it fails I go to IIS, under my virtual directory I reset the permissions - in all tasks -> permissions - and after that it works).

    Does anyone knows if this is a valid solution?
    And if so, why sometimes it works and sometimes don't?

    Anyway... it seems I'm moving in the good direction... at least it was possible to perform detaches and attaches from remote machines... but it's not 100%.

    News suggestions, opinions, everything that could help me to solve this, is welcome Smile

    Tânia
    Tuesday, July 08, 2008 5:29 PM
  •  

    sa uses SQL Security -- therefore it would have the file access permissions of the login associated with the SQL Server service.  I think the key to clearing up your problem will be to observe the file permissions (using windows explorer, right click the file and look at properties) after you detach.  If the only user listed in the permissions is the user you used to detach (or the login associated with the SQL Server service), it's likely the "quirk" I described above is your problem, and the workarounds I described above will get you past the problem.

    Hope that helps.

    Tuesday, July 08, 2008 8:01 PM
  • Hi again!

    I just can't figure out the issue!!
    Now it seems to me that I can't copy a database file if I previously attached the database into sql.

    What is happening is something like this:
    - I attach the database A
    - I detach the database A
    - I attach the database B
    - I detach the database B
    ...

    Now, if I want to copy the files from one of the previous databases, I just can't. I get a permission denied error or access to the file is denied, can't open the physical file...

    This makes any sense? What can I do to solve this?
    Is there a easy solution?

    As I said before at this point my configurations are the following.
    - I log on with an administrator account in sql sqerver (I've changed at sql server configuration manager).
    - My string connection to the database uses a user created by me (I don't know if using this user or sa makes any diference).
    - My sql version is: Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)   Feb 26 2008 18:15:01   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2.

    I don't know what else I can say that can make any diference.

    [...]

    I found on http://msdn.microsoft.com/en-us/library/ms189128.aspx a table that shows the permissions set on the database and log files after an attach or detach operation is completed, and whether the connecting account can be impersonated by the Database Engine. The table is the following:

    Operation Connecting account can be impersonated Files permissions are granted to

    Detach

    Yes

    Only the account performing the operation. Additional accounts can be added by a operating system administrator if they are needed after the database is detached.

    Detach

    No

    The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group.

    Attach

    Yes

    The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group.

    Attach

    No

    The SQL Server (MSSQLSERVER) service account.


    Anyone can tell me what "Connecting account can be impersonated" means? Because I think that probably I've that property (I don't know if it's a property) set to yes and then when I detach, somehow, I loose all the permissions for the files. I would like to know what is this property and where I can change it, to set is value to NO and try to better understand my problem.

    Thanks in advance,
    Tânia
    Friday, July 11, 2008 12:44 PM
  • If you work with Vista, run SQL Server "as Administrator". It worked for me. I had the same error.

    Friday, July 18, 2008 5:50 AM
  • My solution, which might be of help to someone for the

     

    CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'D:\Databases\<filename>.mdf'. (.Net SqlClient Data Provider)

     

    problem is fairly simple.

     

    The problem would occur when the database was first detached from the SQLServer Express 2005 sp2 server, since it could not then be reattached, because of the above error.

     

    I believe that this is a hack and I do not really understand why it works, but I added my user explicitly to the security list of the files, gave them FULL CONTROL and from there I could attach the databases again.

     

    It works, but is not elegant.

     

    Kate

    • Proposed as answer by Aniketm Sunday, July 24, 2011 2:54 AM
    Monday, July 28, 2008 7:58 AM
  • Did what you suggested, but I still can't attach the database file. If I try to open it it shows an access denied to the path requested (message is in spanish).  Any other suggestion you could provide? Thanks.

    Thursday, September 18, 2008 11:03 PM
  • Thanks.  Vista and its securities. got it working

     

    Wednesday, October 29, 2008 6:12 PM
  • read all posts. solution has been posted. it's a permission issue.
    Thursday, November 06, 2008 6:43 PM
  • I had same issue on Vista machine. I run "SQL Server Express Management Studio" as "Run as administrator" and it worked. Smile
    Thursday, December 04, 2008 3:41 AM
  • Hey Marc Enggist thanks for your suggestion. it worked for me. I simply right clicked on the SQL Server Management Studio and ran as Administrator...thats it 
    Thursday, May 28, 2009 3:20 PM
  • Hi Lorez,

    You described this problem exactly as I experienced it. After a database detach, Vista retains only the file permissions for the user who just did the detach. We also found the work-around by changing the file permissions using Window Explorer each time another user needs to re-attach the database. However, this has become a very annoying problem as we have our entire process automated by SSIS packages for the users (more than one user). So, before a user starts the SSIS package, he/she needs to remember to add the file permissions for "everyone" to the database files. Otherwise, the package fails due to "Acces denied - Error 5". Is there a cmd line that I can use to add the file permissions for "everyone" to the database files programmatically other than Windows Explorer? 
    Wednesday, June 17, 2009 4:31 PM
  • I am also attempting to figure out a programmatic way to alter the file permissions on the file, but haven't gotten there yet.  There doesn't seem to be a way, even for a member of the administrators group, to programmatically alter permissions on a file detached by another user.  In most cases where permissions are extremely limited, changing the owner has to be the first step, then the file permissions can be changed, however for some reason on these detached files I can't even get ownership to change...

    /// <summary>
    /// Attempts to retrieve ACL from file, changing ownership if necessary.
    /// </summary>
    private static FileSecurity GetFileSecurity(FileInfo target)
    {
        FileSecurity acl;
        try
        {
            acl = target.GetAccessControl(AccessControlSections.Access | AccessControlSections.Owner);
        }
        catch (UnauthorizedAccessException)
        {
            // viewing permissions is denied -- grant self ownership and try again.
            acl = new FileSecurity();
            acl.SetOwner(AppIdentity.User);
            target.SetAccessControl(acl); // still fails on files detached from SQL Server by another user, even if I'm an administrator!
            acl = target.GetAccessControl(AccessControlSections.Access | AccessControlSections.Owner);
        }
        return acl;
    }
    Wednesday, June 24, 2009 5:23 PM
  • Pazout or anyone else. Do you know how to specify this type of install in a VS2008 setup project for the SQLEXPR32.exe? I can't seem to find a way to specify parameters for the setup of SQL EXp during the setup on a client machine. 


    THanks, 
    Santiago 


    Application Architect - Florida's Turnpike Enterprise
    Tuesday, June 30, 2009 3:27 PM
  • Hi,

    I am a little out of my element here... I'm looking for answers anywhere I can.

    I am running Vista 32 and I am having a problem with an SQL database for Outlook 2007 Business Contact Manager.

    In a nutshell, my workstation crashed (chkdsk corrupted the system rendering it unbootable). To make matters work the back-ups which were stored on an external HDD would not verify, and therefore useless for a full restore. I was able to recover most files from the backup image.

    Business Contact Manager uses two SQL files... a .mdf and a .ldf. I have fairly recent copies of these two most important files; but, no true backup files.

    I have tried several different work-arounds to get Business Contact Manager to recognize these files. One of the errors I get which relates to this blog is Access Denied Error 5.

    I have downloaded and run the Microsoft SQL Server Management Studio Express application; but, I can't locate or work with any of the files that have been created, other than a new database file that I created.

    I could sure use some help in terms that a non-IT guy could understand.

    Maybe it won't be possible to reuse the files; but, I hope that isn't the case.

    Any help would be appreciated.

    Thanks

    Jeff
    Tuesday, July 07, 2009 1:56 AM
  • This is an annoying problem. I read all of the posts and I have to agree that this issue is caused when you detach the database in Vista.
    I resolved it by attaching the database in Windows Xp and then detaching it.

    Also, this issue can be solved by giving FULL CONTROL to the current user, but since I'm making a Installer, giving that is not quite a good idea, and I don't know how to do it anyways...


    Hope that's help somebody that not solved the problem yet.
    Get a life. Go party!
    • Proposed as answer by JohnH123 Thursday, July 23, 2009 4:45 PM
    Tuesday, July 14, 2009 12:23 AM
  • It could actually be somewhat more complicated.  If the database is setup from the local administrator account (it is frequently difficult to set it up any other way - SQL has to look for specific permissions which the user of a domain may not have when the program is setup initially), to continue, it the local administrator was the user that setup the accout and for some reason, chose to set it up using network services as the access account, then the password for the local administrator was passed as the primary securable.

    When this occurs, you should be able to list the service account (services.msc) and see that the [log on tab] owner is the local administrator who is not a member of the domain (although the machine might be) and thus, any transparency in the SIDS on the domain have disappeared.

    The thing to do is to decide which account is to be used as the service account (possibly your own account) and reset the log on as service rights to use the service account (again, possibly your account).  Once you choose to start up the SQL Server (MSSQLServer) service under your own account, it will reset the local permissions and allow you to create the database. 

    I think it is a bit too complicated to explain properly.  I can't quite grasp why the setup cannot run under the domain account and perhaps for the express version, this is not the case, but if you are using the developer version, chances are good that the specifics of the securables may be listed under the local administrator.  If you are on a domain, you may or may not even have the local administrator password account, thus, probably the ONLY way you will be able to reset the securables is through a change in the logon with service rights for SQL (MSSQLServer)

    Anyway, worked for me.   Forgot to add - obviously for a developer version, it would not be encouraged for someone to be sharing the database so it would be normal for difficulty to occur trying to create a developer database as accessible on a network (obvious reasons).  But the situation was similar on a server when connected to the network and installing a copy of 2008 enterprise.   I suspect there is a solution here but it is being stumbled over in this thread.  Perhaps it can only be installed on the network using an elevated domain service account (one with administrator priveleges?).  This part doesn't seem sensible, so I continue to believe there is a better solution other than installing to the local admin account.
    • Edited by Crakdkorn Saturday, September 12, 2009 1:17 PM Believe there is something missing in the thread.
    Tuesday, September 01, 2009 1:16 PM
  • Yes, Marc!

    Runnning Microsoft SQL Server Management Studio as Administrator was the true answer for my case under Windows 7+SQL2008.

    Thanks!
    • Proposed as answer by angus001 Thursday, November 12, 2009 10:49 AM
    Monday, November 09, 2009 2:55 AM

  • I had this problem. I found that after I'd detached it, it removed the default security permissions on the mdf and ldf files. I simply selected both when into the security tab and clicked advanced, allow inherotable to yes and clicked apply...  
    Monday, November 09, 2009 4:51 AM
  • Good God, talk about making a product difficult to install and use.  This was a bloody nightmare.

    Someone else mentioned it too.  Selecting SQL Server Management Studio in the Start menu (or wherever), right-clicking and selecting "Run as Administrator" worked.

    I wasted an entire day installing and uninstalling.

    No wonder people go to Macs! SQL Server 200* Express is meant to be for hobbiests and beginners, etc.  It would scare most people away.  During the install when it asks for a database engine account even Microsoft's own video shows the narrator selecting "SQLSERVER" as the user.  Where did they get that from?

    Anyway, for me (and others) the solution to be able to attach to a database seems to be to run SQL Server Mangament Studio as an Administrator (different from logging in with Administrator rights).

    Urgh, I'm exhausted! 

    I got to a chapter in a SQL Server book that tells me the rest of the book now uses the Northwind sample d/b and is included on the CD.  It isn't included on the CD and has been a nightmare to install.
    Thursday, November 12, 2009 10:49 AM
  • 5123 CREATE FILE encountered operating system error comes due to security in mainly server operating system. this is problem of Access security.

    Reason

    File Read only
    Access Permission

    Operating System

    Mainly in Server System Windows 2000,2003,2008 due to NTFS Security.

    Solution

    Give full permission to database files.

    1. Right Click on file .mdf and .ldf in security tab
    2. click add>>advaned >> find now >>select everyone >> click ok >> ok
    3. in allow click full control >> ok then attach database (give same permission to both .mdf or .ldf)




    Virender Verma Software Engineer www.virender.net
    • Proposed as answer by Red_Eyes Sunday, December 13, 2009 9:36 AM
    Tuesday, November 24, 2009 6:05 PM
  • This is definately a security issue and the posts in this thread all fix a particular instance of it... but are not necessarily the complete solution. I have found the following solution has worked for me:

    1) Create a brand new database called "Test" (or anything you like)
    2) Navigate to the MDB file through Windows Explorer
    3) Right Click, and chose the properties menu. (You may get an extra Warning from Vista/Win7 depending on your settings)
    4) Click Security tab
    5) Look at the "Group or user names" and thier rights (Possibly take a screen snip)
    6) Make sure the database you are trying to attach has the identical properties. (One will be something like SQLServerMSSQLUser$xxxx$MSSQLSERVER)

    The problem arises becuase the file is most likley tobe owned by a non existant user on the system you are working from.



    • Proposed as answer by peter80608 Wednesday, December 30, 2009 7:51 PM
    Sunday, December 13, 2009 9:42 AM
  • This issue drove me crazy! I worked on it on Vista + SQL Express 2008 the past several days. I did not have any problem on detaching and attaching database either from command line or in SSMS when I logged in as Windows Administrator. But I had problem if I logged in as Windows Standard User, which is the case for most of our customers. So I had to solve it.

     

    After Googling and reading MSDN and a lot of testing, finally I found the answer:

     1. From command line, use Sqlcmd application instead of Osql application with user SA and its password. Or

     2. Use SSMS, with SQL Server Authentication, login as SA.
    The reason is only Windows Administrator and SQL Server service account have access to the database files. I think Sqlcmd and SSMS  impersonate the right SQL Server service account, but Osql does not.

     
    Good luck!

    Wednesday, December 30, 2009 9:39 PM
  • I had same issue on Vista machine. I run "SQL Server Express Management Studio" as "Run as administrator" and it worked. Smile
    Same issue on a Windows 7 machine. Worked like a charm. Thanks
    • Proposed as answer by Biloff Friday, June 04, 2010 11:35 AM
    Friday, June 04, 2010 11:34 AM
  • thanks!
    Thursday, January 13, 2011 6:04 AM
  • thanks man you make my day its  work for me
    hhussain
    • Proposed as answer by TigerCare Monday, February 14, 2011 7:58 PM
    Friday, February 04, 2011 8:15 PM
  • Solution:

    right click on SSMA (MSSQL management studio) and start it 'as administrator'.

     

    Everything should work fine.  If still trouble, then login under the same credential for SQL server service account and repeat the above.

     

    Mohammed Huda

    SQL server solution Architect

    www.Computerprofessioanls.org

    • Proposed as answer by Gierasimov Friday, July 13, 2012 6:09 AM
    Saturday, February 19, 2011 3:27 PM
  • In order to successfully run a programmatic attach of a database with SQL (Express), the account configured to run the service (Check via the "SQL Server Configuration Manager") must be granted "FULL CONTROL" access on the folder where the database files are located. "MODIFY" access right is not enough !

    V.
    Friday, March 25, 2011 12:11 PM
  • I read thru all the posts on here and didn't see what solved this problem for me.

    I re-downloaded this Adventureworks2008R2_data.mdf file and after it fininished downloading, I right-clicked it and selected "Properties" and then selected the "Unblock" button. Do this before moving the file anywhere after downloading it.   Viola!

    I seem to recall needing to use the unblock button before. But this was the trick to allow me to attach the file.

     

    Wednesday, June 29, 2011 9:11 PM
  • the solution for me was simple, i closed sql server managment and reopened with the admin user. After that i could attached the database file. Hope this help guys.
    • Proposed as answer by d.allen101 Tuesday, August 16, 2011 7:01 PM
    Sunday, July 17, 2011 9:47 PM
  • I don't understand why virtually NO one is listening to Serdna and few other's. The solution is simple i.e. (LOG IN AS ADMIN)! Hope this finally helps. 
    donald
    Tuesday, August 16, 2011 7:03 PM
  • This worked for me also - Windows 7
    Tuesday, November 22, 2011 11:04 PM
  • The error 5, accessed is denied.

    By default, MSSQL instance is run under Network Service account, so you have 2 solutions:

    1. Change Log On As to Administrator Account as mentioned above

    2. Or granting "Network Service" with write permission to your working folder.

    Problem will be solved.

    Correct if me wrong. :D 

    Monday, February 27, 2012 11:36 AM
  • The following helped me:

    1.) create a new folder

    2.) edit the properties\security of the folder

    3.) add the user everyone with the option 'change' 

    4.) copy the mdf ldf into the new folder

    5.) try to attach

    6.) if it worked, detach, copy into original path and attach. 

    Monday, October 08, 2012 9:36 PM
  • Launch SQL Server Management Studio on the database server as Administrator ("Run as administrator").

    The error is a red-herring because the SSMS when running under your account doesn't have the mojo to work with the database files. Likely the SQL Server service and administrator accounts do so get yourself out of the way and let the big boys do their job.

    Friday, July 05, 2013 4:29 PM
  • Hi, I solved this problem when I place AdventureWorks2008R2_Data.mdf to next location C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup. It is a folder of MSSQL backup files.

    Cheers

    Saturday, July 13, 2013 2:02 PM