September 13, 2011 at 3:57 pm
A little background..
I have a user who wants to be able to refresh a copy of his test database from production on the fly. He needs to be able to test changes to a table using the most current data and be able to kick off the restore any time of day. I thought I had this figured out, but recently came to a halt in my progress. I planned on creating a job and granting this user as job owner (plus necessary msdb permissions). This job would be 3 parts:
1) Backup the database from production. I created a link server to production db using an account that only had db_backupoperator and db_datareader to target DB. This backup would then be placed on a fileshare. This stage works.
2) Included a series of scripts I modified that goes out to the production server and queries the backup job history to compile a list from the backup taken in step 1 to populate variables of database name, backup location, etc. These variables then populate a dynamic restore script that would allow the restore to take place on the test server. This step fails.
3) Sync users. This step works
The errors I am getting in Step 2 are due to the fact that the user that will be running this does not have high enough rights. Is there a way that when the user runs this job, and the job gets to step 2, that the step can run under a context higher then the users right temporarily to allow the restore? If not any other suggestions to accomplish this without granting the user sysadmin?
These systems are running SQL Server 2008 R2 patched to 10.50.1600 and higher.
Thanks!
September 13, 2011 at 4:20 pm
Smiley77 (9/13/2011)
A little background..I have a user who wants to be able to refresh a copy of his test database from production on the fly. He needs to be able to test changes to a table using the most current data and be able to kick off the restore any time of day. I thought I had this figured out, but recently came to a halt in my progress. I planned on creating a job and granting this user as job owner (plus necessary msdb permissions). This job would be 3 parts:
1) Backup the database from production. I created a link server to production db using an account that only had db_backupoperator and db_datareader to target DB. This backup would then be placed on a fileshare. This stage works.
2) Included a series of scripts I modified that goes out to the production server and queries the backup job history to compile a list from the backup taken in step 1 to populate variables of database name, backup location, etc. These variables then populate a dynamic restore script that would allow the restore to take place on the test server. This step fails.
3) Sync users. This step works
The errors I am getting in Step 2 are due to the fact that the user that will be running this does not have high enough rights. Is there a way that when the user runs this job, and the job gets to step 2, that the step can run under a context higher then the users right temporarily to allow the restore? If not any other suggestions to accomplish this without granting the user sysadmin?
These systems are running SQL Server 2008 R2 patched to 10.50.1600 and higher.
Thanks!
The User should not need permissions to the production server.
Do you want this user to be able to back up the Database at any given point in time without coordinating with Production DBA Staff?
I firmly believe in refreshing your QA & Dev Environments.
Be careful on Linked Servers because of potential Security Risks.
I looked at the permissions that you assigned and you may have assigned to the wrong server.
You never want to make them a member of the sysadmin fixed server role.
They need read access to the share where the backups are stored.
If there are not any current backups that meet their needs, simply ask that the backup job be kicked of.
Then they can restore the database all that he wants.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 13, 2011 at 4:34 pm
Self service restores can be a little dicey, but I've been in places where QA had that level of control. As far as the job is concerned, the job is being run either as the Agent or using the proxy you set (the Run As box in the step), and those logins should be given the necessary permissions if you don't want to give them to the user directly so they can run the scripts manually.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 13, 2011 at 6:21 pm
Thanks for the responses. Let me explain a little more of my situation since I wrote the post in a hurry. I am the primary DBA (with a backup who focuses on Oracle primarily)of over 400+ databases around the globe. Prior to my employment a lot of users had access that I frown upon and have cleaned up the security of these databases significantly. In this case the technical owner, which is what we refer to the owner of the application whose database is in question, had inherited sysadmin rights due to carelessness of the previous DBA allowing the technical owner access by being a member of the BUILTIN\Administrators group in early release of 2005. I have migrated this user to a SQL Server 2008 R2 standard database with the understanding that the rights he once had are to be taken away. The number one concern is that when the agency to which the application was written for updates codes and rules to the application the change needs to go in place ASAP. The procedure they followed was to take a live backup of the database and restore to a test database (at the time this was the same server, but now I have test and prod seperate for this instance). At that point the technical owner would run scripts against this database and the users would test then apply the changes to production until the next change. If this was a regular occurrence then I could create a job and schedule it with enough permissions to backup the database and restore to test, but the timeframe to when this change may need to come about is not regular. This backup is also provided to the vendor, which is overseas along with the technical owner. This company has a total of 3 DBAs (Sr SQL, Sr Oracle, SQL\Oracle). In our group we really don't have the flexibility to wear the hat of production, and quality\dev database administrator. We rely on our technical owners quite abit. I have a tendency of being as strict as I can with security (no sysadmin unless dba, approved generic accounts, no remote desktop access, etc) without forcing a bottleneck. So what I am trying to accomplish here is to allow this Technical owner to run a job from Management Studio by connecting to to the Test or QA instance. Like I said previously, there are three steps to this job. First step creates the backup on the fly using a link to the production instance with only enough permissions to run the backup (db_backupowner) of the database he owns. This backs up to a file share on the application server (non database server) with a format of <name>_yyyymmdd_hhss. In step 2, the job accesses the backup history in the prod msdb database (read_only to table) to find out the name of the file of the backup (since its dynamic) and populate several variables such as backup file name, path, etc. These variables are used to populate a dynamic restore script and then execute. This step would succeed if I run it as dba credentials, but fails if the Technical Owner tries to run this step. The third step is just simply to fix the sql user-logins after the restore. I figured proxies might be the route I needed to follow, but my lack of experience with using proxy accounts has me a little in the dark. I will research in the morning and see if I can come up with a solution using proxies. In the meanwhile, besides not allowing this user to run the job and having to be woken up all hours of the night, is there a better way to handle this situation? Thanks for checking out my post amd offering assistance!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply