none
BACKUP DATABASE TO DISK: how to change the Windows User used to save a backup to disk?

    Frage

  • Hi everybody,

    we face an issue with a Maintenance Plan containing several Backup tasks which save to a shared directory.

    Due to new IT-Security standards, the Windows User used to save the backups to disk has to be changed.

    Do you know if the the Windows User can be changed within a Backup or T-SQL script task. Or should we better set up a SSIS package for our Database backup process?


    Regards, Arcus (working on SSRS 2008 R2)


    • Bearbeitet arcus4.28 Donnerstag, 14. Februar 2013 12:07
    Donnerstag, 14. Februar 2013 11:03

Antworten

  • Hi there,

    although you're in the german forum, I'll try to answer your question.

    As far as I know, it's always the service accounts, under which the sql server runs, that needs the read/write permissions on the location where the backup files are placed.

    So if either you send a backup database command directly to sql server  or run the command via SSIS or any other script task, you have to take care that the executing service account has the appropriate rights. It has nothing to do with the user which runs the package or the T-SQL.

    Regards

    Dirk


    May you never suffer the sentiment of spending a day without any purpose



    Donnerstag, 14. Februar 2013 11:54

Alle Antworten

  • Hi there,

    although you're in the german forum, I'll try to answer your question.

    As far as I know, it's always the service accounts, under which the sql server runs, that needs the read/write permissions on the location where the backup files are placed.

    So if either you send a backup database command directly to sql server  or run the command via SSIS or any other script task, you have to take care that the executing service account has the appropriate rights. It has nothing to do with the user which runs the package or the T-SQL.

    Regards

    Dirk


    May you never suffer the sentiment of spending a day without any purpose



    Donnerstag, 14. Februar 2013 11:54
  • Thank you for your quick answer Dirk!

    If the Service account to run the MSSQLSERVER Service is changed to a different Windows User, this User should be used automatically by the script task to save the DB backups to disk, right?

    We will test it...well it works, thanks very much again!


    Regards, Arcus (working on SSRS 2008 R2)

    Donnerstag, 14. Februar 2013 12:24
  • It is the account of the SQL Server Agent, not the MSSQLSERVER service!
    Just my 2 cents.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    http://www.insidesql.org/blogs/cmu/

    Donnerstag, 14. Februar 2013 12:25
  • It is the account of the SQL Server Agent, not the MSSQLSERVER service!


    Hi Christoph,

    are you sure?

    You may schedule a backup under the SQL Server agent, but the command itself is runs under SQL Server service account.

    Look at http://msdn.microsoft.com/de-de/library/ms187510.aspx

     Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.

    Regards and have a nice day


    May you never suffer the sentiment of spending a day without any purpose


    • Bearbeitet Dirk Hondong Donnerstag, 14. Februar 2013 12:46
    Donnerstag, 14. Februar 2013 12:45
  • Oh yes, you're right!

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    http://www.insidesql.org/blogs/cmu/

    Freitag, 15. Februar 2013 12:39
  • Hi,

    first of all this is a German forum. In future please either post your question in German or post on the English Forums.

    In this situation, was your problem solved?

    If yes, please also mark as answer the replies that helped you, so that others can profit from this.

    Regards,
    Raul


    Raul Talmaciu, MICROSOFT 
    Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Montag, 18. Februar 2013 10:07