Post reply

Backup error

  • December 7, 2009 at 1:04 am

    #137473

    Hi

    While trying to take backup from SSMS (2005) I face the following error:

    " TITLE: Locate Database Files - Servername

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

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

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

    If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."

    However TSQL code works fine.

    I login using SQL authentication. It has db_owner, db_backupoperator rights on db

    SQL Server runs as local system account.

    When I login with windows authentication .. and try to take backup it does not pop me error, this happens only with SQL login which do not have sa rights

    SQL Server 2005 Dev edition SP3

  • December 7, 2009 at 2:12 am

    #1088405

    an abstract from the BOL:

    "BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles."

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/89a4658a-62f1-4289-8982-f072229720a1.htm

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • December 7, 2009 at 3:08 am

    #1088415

    Does this mean that only login which belong to sysadmin role can take backup.

    If i would not want to give sa rights to other person but give backup/restore rights what would be the path then?

  • December 7, 2009 at 3:59 am

    #1088430

    yes...u are right...I think that there is no other way...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • December 7, 2009 at 4:39 am

    #1088437

    Users only need to be members of the fixed role db_backupoperator to be able to take backups, they do not need to be system administrators, as mentioned by Sqlfrenzy.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • December 7, 2009 at 8:31 am

    #1088599

    Carolyn Richardson (12/7/2009)

    Users only need to be members of the fixed role db_backupoperator to be able to take backups, they do not need to be system administrators, as mentioned by Sqlfrenzy.

    thanks for correcting me....I got confused from the statement mentioned in BOL and didn't check it myself....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply