none
CREATE FILE encountered operating system error 5(Access is denied.) - On the same server

    Question

  • All,

     

    I have SQLServer 2008 installed on my workstation which runs under the local system account with my id being the sysadmin. I have created a database EMRDB under the C:\sql folder with the file names C:\EMRDB_Data.mdf and C:\EMRDB_Log.ldf.

     

    I have a Windows Account (Domain account) – AcctDM\abclogin  with which I login to SSMS and I also have another login defined in the same way (AcctDM\xyzlogin) and both are sysadmins on the server. When I detach a database on the server the other user (with AcctDM\xyzlogin) cannot attach the same database on the same server (from his workstation via SSMS). The following is the error that is seen

     

    <<CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\EMRDB_Data.mdf’. (.Net SqlClient Data Provider)>>

     

    After I granted “Full Control” on the file for the Windows Login (AcctDM\xyzlogin) then he is able to attach the file (database). I granted Full Control by right clicking on C:\EMRDB_Data.mdf and C:\EMRDB_Log.ldf from Windows Explorer, Clicking on the Security Tab and granted Full Control.

     

    I was wondering if there is a better way to handle this because of the number of users. Any ideas?

     

    Best,

    MS

    Tuesday, December 06, 2011 8:15 PM

Answers

All replies

  • No, this behavior is described in the BOL:

    http://msdn.microsoft.com/en-us/library/ms189128.aspx

    File system permissions are set for a number of options as a security precaution.  I wouldn't recommend that you put the database files in the root of the C Drive.  That is just bad overall security wise.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, December 06, 2011 8:32 PM
    Moderator
  • I mentioned C:\ root just for discussion.

    Is there a workaround to adding the permissions to each of the users individually?

    Tuesday, December 06, 2011 8:49 PM
  • What specifically is the problem and what are you trying to do?  This would almost seem like a SQL Express User Instance problem rather than a problem for SQL Server in general, so the context of the problem really matters here.  Don't try to be general with what you are doing, be very specific about what it is that you are trying to do and the problem that is being encountered, so that we can be very specific with the recommended solution to the problem at hand.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, December 06, 2011 9:02 PM
    Moderator
  • As Jonathan said, this is working as designed.  When you detach a database the files get assigned rights so only the person who detached the database has access to the files.  This is for security reasons.  Users should not be detaching and attaching databases. 
    Wednesday, December 07, 2011 1:22 PM
    Moderator