locked
Execute a job that Restores a DB; RRS feed

  • Question

  • Hi I am running a sql 2k8 EE sp3 server, I have a sql agent job that restores a db on a particular server. There is a user in db that would like to start and stop the job on an adhoc basis, Currently job is owned by sa ,Is there any way a user can run this job without being sa?

    I created a credential , and tried to use a SQL Agent proxy with the credential and grant login(user uses sql login) to proxy. But don't seem to find a proxies for Backup/Restore Database?

    Also the job executed by that user needs to add other sql/windows logins to the Db restored.

    Thanks


    • Edited by SQL_Jay Thursday, December 29, 2011 5:31 PM
    Thursday, December 29, 2011 5:20 PM

Answers

  • You could write a stored procedure that starts the job, and which the user can call. There are two different techniques you can use to package the permission to start the job with the procedure.

    I will not go into details here, but refer you to an article on my web site, as this article includes exactly examples for starting jobs:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by SQL_Jay Friday, December 30, 2011 1:58 AM
    Thursday, December 29, 2011 10:44 PM