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!
Answer
Solution:
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:
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.
Q&D?
yes.
beware: double check the task settings to be sure that 2 restore jobs cannot be run at the same time.