Post reply

Using OSQL to call a Job which backups a Database

  • June 25, 2004 at 2:34 pm

    #152203

    Once a month I need to run a backup of a database at the request of the company controller just before he runs certain procedures in his application. The time when he needs this backup varies, and I essentially have to be 'on call' for him.

    What I would like to do is create a solution for him to backup the database whenever he is ready himself. Since the backup job already exists as a SQL job. I thought I would have him use OSQL to call the following.

    USE msdb

    EXEC sp_start_job @job_id = 'DC8FB20B-4F4C-4EF6-8D0E-EEAF4766D793'

    The problem I am having is with permissions. Everything works great for me but I am a sysadmin.

    My question is what are the minimum permissions I need to create for him to have the rights to kick off the job. Also where do these permissions need to be set. MSDB, target database?

    If there is a better way to handle this, I am open to that as well.

    Thanks,

    Shain

     

  • June 26, 2004 at 4:41 am

    #512075

    Not sure what else your job might have been set up to do, but if it's only to backup the database, granting the user the db_backupoperator database role on the database he's using ought to be enough.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • June 28, 2004 at 2:42 am

    #512119

    I would geuess that the job is owned by 'sa', and the user that would like to kickstart the job is not.

    They way to get around this is:

    1. Change the owner of the job to the person who gonna kickstart it.
    2. Setup permissions for the person so that he can backup the database. The lowest rights for backing up the database is db_backupoperator in the database.
    3. Make sure that the person has the correct ntfs-rights to backup to the path specified in the backup-job.

    Good luck...


    robbac
    ___the truth is out there___

  • June 28, 2004 at 9:13 am

    #512210

    Robbac,

    That was it exactly

    Thanks for your help guys.

     

    Shain

Viewing 4 posts - 1 through 3 (of 3 total)

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