May 1, 2020 at 7:55 pm
I am looking a way to restore production data to our dev and QA environments for user database. Instead of manual restores, I would automate using a sql Agent Job so that developers can run on demand. So grant permission to that particular agent job to run manually. The database should not lose user accounts or logins after the restore from prod backup. The backups would have data and time stamp so the script should pickup the new file every time the Job is run to restore. Has anyone implemented this? Do you see any issues with this solution? Thanks in advance!
May 1, 2020 at 8:06 pm
yes... but....
DO NOT restore directly from prod to any other environment. where you do need to have production data always setup a process on another server (restricted, to be treated as production, but with a developer license) where you have a process to replace and/or remove any PII/GDPR data.
After you do the "cleaning" then you create backup files that can be made available for other environments to restore on demand.
For the restore - how will the job know which db to restore to? although you could have a default job working like you said that always restores to the same DB the developer may wish to restore to another db name.
one possible way, which I have implemented on my shop is to have a small web app which lists the available backups and available servers/disks/space available and allows the "user" to restore a particular backup onto a particular server/database.
May 4, 2020 at 10:18 pm
Great. Curious how you implemented that? Would that fall into big project?
On a side note, the Microsoft SQL Server database role SQLAgentUserRole apparently lets a member create/delete SQL Agent jobs along with viewing jobs, job steps, and results. I would just like to grant user a particular Agent job with execute rights only and not allow to view or execute any other jobs on that particular server. Is that possible?
May 4, 2020 at 11:05 pm
Would not fall on a big project - small web page, 1 or 2 "pages" so it is something that even a junior Web dev can do.
for starting the job - you can if you follow the instructions here
adapt to have a SP that starts the specific job and that job alone and it will work fine.
there may be other ways but I'm not aware.
May 5, 2020 at 10:35 pm
Yes, but several caveats. The problem with the msdb operator roles is that they grant rights to the user to do these things on ALL Agent Jobs, not only the ones you want them to be able to run.
One way around this it to assign the user who will be running the job as Owner of the job, because the owner will be able to run the job regardless of whether they are in those groups, and will not be allowed to run jobs the user doesn't own. This is fine if it will only be one individual, but if it is a group of people you cannot assign an AD Group as an owner.
May 5, 2020 at 11:03 pm
Thanks Dan! So once i change the owner of the job to one individual, does that mean he can able to view and execute that particular Job only? However, i have group of people.
May 5, 2020 at 11:39 pm
did you look at the instructions link I supplied? that would allow you to do it without granting permissions directly to the user
May 6, 2020 at 2:56 pm
One last thing, you noted "The database should not lose user accounts or logins after the restore from prod backup". Logins are an instance (server) level object, so database restores outside Master don't affect these. However, users are database objects. When you restore a database, you get the users in the database. If you have dev/test users, they need to be reset/recreated and mapped to logins. You can do this in the stored procedure that runs the job.
May 7, 2020 at 12:30 am
Frederico, i looked at link and it worked for DBCC. However, when i try for restoring the database from backup file share location and i am using sys.xp_cmdshel and then trying to take the database to single user mode with Alter command when the database is in use and use master doesn't allow within the store proc. So i am testing with sql account and i get below error. I am still working on it.
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
May 7, 2020 at 5:45 am
link is to allow you to give permissions to start a job - it has nothing to do with what the job does afterwards.
so separating the 2 things - did you manage to successfully implement the instructions from the link as a way to allow a user to start a job that he does not own, and while he has to other permission giving him access to start jobs.
for the job itself if it is failing you need to give a lot more details than that including posting the code.
May 8, 2020 at 11:04 pm
I was thinking of requesting generic AD account for testing to run the restore Job with minimal rights instead of my own account, since i am part of the Sysadmin group and the person who will run this Job will not have sysadmin rights. So that way i can identify the errors and fix them as i see and test the process thoroughly. Is that sounds reasonable to you?
May 9, 2020 at 3:21 pm
Yes, I'd certainly use a separate account for this. Should be able to be a backupadmin and then perhaps file system rights if there is a copy involved.
June 11, 2020 at 7:33 pm
Thanks!So now I am trying to build the restore Job with minimum rights?Before restore trying to take db in single user in case any open connections.
ALTER DATABASE DB name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE DB
GO
Then run the restore process which will use xp_cmdshell.
However, now i have to grant Control database and excute on xp_cmdshell to the user. Do you see any issues doing this way?
June 11, 2020 at 9:23 pm
you have not understood anything that was transmitted to you.
you setup a job - that job restores a particular database from a particular location - if name and location is always the same then its even better. Job is owned by the account that is the owner of the database to restore - using restore as it requires less permissions than drop/create database.
you then, following the link I gave you, setup a proc that has the permissions to start this particular job - and you give permission to your user to execute this proc - no other permission is required.
so when the user wishes to restore the database he just executes the proc - and all work is done under the high priv user.
xp_cmdshell is not needed for anything at all - if you really need to access filesystem to list files then do that with a powershell step
To access the filesystem you will need to setup a proxy account if you require use of powershell to list and/or copy backup files - but note that you can restore from remote network location
June 11, 2020 at 9:40 pm
Thank you sir
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply