Re-creating users after database restore

Question

Re-creating users after database restore

SQL Guy 1

SSCoach

Points: 15981
More actions
October 10, 2012 at 2:59 pm

#263735

Here is my scenario: I backup a production database and restore it to dev environment. But after the database is restored, I don't want to have users that have been brought from production, because we have completeley different users, roles, permissions between production and dev.
For that reason, prior to restore, I script out all users along with their permissions in dev. After restore, I drop all users and apply the script generated previously. When I run it manually, it's OK. But all this work should be automated and put into a job. And the problem is that how I can pass that script from step to step ? I already tried ##global_temp_table, but it is not getting created from inside of a job.
What other ways, and what I mean is safe and reliable ways, to temporary store the script ?
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

if a table will do the trick, why not create and then drop a permanent table in master or msdb to hold the script you are generating for a ##globaltemp table?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

SQL Guy 1 (10/10/2012)
Here is my scenario: I backup a production database and restore it to dev environment. But after the database is restored, I don't want to have users that have been brought from production, because we have completeley different users, roles, permissions between production and dev.
For that reason, prior to restore, I script out all users along with their permissions in dev. After restore, I drop all users and apply the script generated previously. When I run it manually, it's OK. But all this work should be automated and put into a job. And the problem is that how I can pass that script from step to step ? I already tried ##global_temp_table, but it is not getting created from inside of a job.
What other ways, and what I mean is safe and reliable ways, to temporary store the script ?
Thanks

This will be easier if it's a job that runs and SSIS package instead of a script, then you have numerous options.

If it has to be a script run by a job, why not create real table (instead of a global temp table) then drop it afterwards?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQL Guy 1 SSCoach Points: 15981 More actions · Answer 3

Thanks for quick replies.

Alan: I cannot create a real table, because this db will be restored, and the table will be gone. About SSIS, frankly I am not so well experienced in it to make such tricky things. But anyway, where I will store generated scripts in SSIS ?

Lowel: I also thought about keeping it in master and msdb, but is it a proper way, like in general, to put in system databases my objects which were not originally belonged there ? And what if by some reason my code will "forget" to drop it ?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

SQL Guy 1 (10/10/2012)
Thanks for quick replies.
Alan: I cannot create a real table, because this db will be restored, and the table will be gone. About SSIS, frankly I am not so well experienced in it to make such tricky things. But anyway, where I will store generated scripts in SSIS ?
Lowel: I also thought about keeping it in master and msdb, but is it a proper way, like in general, to put in system databases my objects which were not originally belonged there ? And what if by some reason my code will "forget" to drop it ?

To clarify - you could create the table on another database. I usually have a DBA DB on each of my instances for storing scripts, etc... You could use something like that or even the MSDB as was suggested.

Re SSIS: You can store the script in the SSIS package in an Execute SQL Task but I would not suggest that if you don't have SSIS experience. I suggest, however, you take a look at the Intigration Services Stairway and get familiar with SSIS as it is good for handling tasks like the one you are dealing with.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001