locked
SQL Server 2008 file permissions RRS feed

  • Question

  • We're converting from SQL Server 2000 to 2008. We've set up a new DB server (Windows 2003 Server), we've installed SQL Server 2008. We've installed a few databases. Things seemed to be going well. Then...

    I'm trying to move over a database from the old server to the new. I've tried detach/copy/attach and backup/copy/restore. I'm putting the file in the default database location (D:\MSSQL\Data or D:\MSSQL\Backup). But then I find that SQL Server can no longer access that location! I get a message that says:

    "Cannot access the specified path or file on the server. Verify that you have the necessary security priveleges and that the path or file exists".

    This as soon as I click on Add on the Specify Backup dialog or on the Attach database dialog.

    I've tried: logging in with SQL Server authent. and Windows authent.; checking and changing permission on the folder and file; basically everything I can think of.

    Suggestions?

    Dana


    Dana
    Thursday, October 7, 2010 5:19 PM

Answers

  • Hi Dana,

    Does the account under which SQL server runs has permissions to these folders ? For the account with which you have logged in , does this have enough access to create a new database ( db creator or sysadmin or create database permissions) ??


    Thanks, Leks
    • Proposed as answer by Lekss Friday, October 8, 2010 6:25 PM
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:28 AM
    Thursday, October 7, 2010 8:53 PM
  • Please ensure the SQL Server Service account has the file level privileges as advised by microsoft.

    Reviewing Access Control Lists Created for SQL Server Service Accounts

    Instid\MSSQL\backup       Full Control
    Instid\MSSQL\data           Full Control
     

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Friday, October 8, 2010 10:49 AM
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:28 AM
    Friday, October 8, 2010 6:22 AM

All replies

  • Hi Dana,

    Does the account under which SQL server runs has permissions to these folders ? For the account with which you have logged in , does this have enough access to create a new database ( db creator or sysadmin or create database permissions) ??


    Thanks, Leks
    • Proposed as answer by Lekss Friday, October 8, 2010 6:25 PM
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:28 AM
    Thursday, October 7, 2010 8:53 PM
  • Please ensure the SQL Server Service account has the file level privileges as advised by microsoft.

    Reviewing Access Control Lists Created for SQL Server Service Accounts

    Instid\MSSQL\backup       Full Control
    Instid\MSSQL\data           Full Control
     

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Friday, October 8, 2010 10:49 AM
    • Marked as answer by Tom Li - MSFT Sunday, October 17, 2010 8:28 AM
    Friday, October 8, 2010 6:22 AM