php - MSSQL: starting a pre-defined database restore-task with minimal user permissions


I'm struggling with the following case. Let me first briefly introduce the infrastructure.

My Infrastructure

  • MSSQL Server 2012

  • PHP 5.4.24 on IIS

The communication between MSSQL and PHP works fine.

On the MSSQL Server I got two Databases one for production (P), one for testing (T). Every evening in workdays I'm doing a backup of P and restoring P to T to have a fresh set of data. This task is done by Jobs within the SQL Server Agent.

One task for backing up, one task for restoring.

My developers need untouched data of the latest backup of P to facilitate their development work. They can't always wait until the job is running in the evening.

Hence I'd like to provide a way, they can trigger the task for restoring from the latest P themselves.

This can be done by stating the query mentioned here:EXEC msdb.dbo.sp_start_job 'Update development'

The issue now is, that I'd like to have MSSQL user U with minimal preferences to run this job on the test database for security reasons. I created U with data writer access right for the test database.

I also followed a hint to wrap a stored procedure around the job:

    CREATE PROCEDURE dbo.UpdateTestDatabase WITH EXECUTE AS owner AS EXEC  msdb.dbo.sp_start_job 'Update testingDatabase';

Finally, I'm also setting the EXECUTE Permission for the stored procedure for U, but I still receive the following error:

Die EXECUTE-Berechtigung wurde für das sp_start_job-Objekt, msdb-Datenbank, dbo-Schema, verweigert.

Translation: EXECUTE-Permission for the sp_start_job-Object, msdb-Database, dbo-Schema has been rejected.

I highly appreciate any kind of advice. Particularly, if there is a better easier approach.

Thanks in advance!




my suggestion is a trick: create a table on the db server accessible to the developers with a single column, schedule a job to read that table every 15 minutes and if the proper value is in that table then the scheduled job will perform the restore from PRO to DEV.

the procedure to schedule could be like tis:

if exists(select field from table where field='dorestore')
 EXEC  msdb.dbo.sp_start_job 'Update testingDatabase';

the job can be limited to run on workdays 8/18.

this is not an interactive solution and is not optimal but the devs are not required to do anything more than setting a field in a table and the job can be started automatically with the expected credentials.


beware: double check the task settings to be sure that 2 restore jobs cannot be run at the same time.

People are also looking for solutions to the problem: php - How can I run a function with an array of checkbox values passed as an argument?


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.