SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Scripting within an Agent Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
645 Posts

Posted - 02/20/2013 :  11:48:01  Show Profile  Reply with Quote
So in our processes for backing up and restoring application databases, the engineers just assumed that backing up the app db was going to capture all the associated users that were relevant to that db. They were wrong. The user accounts need to be scripted out from the msdb database in the system databases.

I've been tasked with streamlining that process, possibly using an Agent job.

what we're trying to avoid is to have to go IN to the msdb database "manually", right click and choose "Generate Scripts" and go through all the steps in that dialog box to create the script to create those user accounts on the new db. Additionally, once that script is generated, it needs an IF EXISTS/WHERE/DROP line(s) added before each "Create User" command.

Ideally, it would be an agent job that scripts out those users from MSDB, adds the relevant IF EXISTS/WHERE/DROP statements for each user between each CREATE USER as step One of the job. Step two would be simply the execution of that script on the new server. Obviously, a create script would be generated of that job and would be run before the restore would be done of the db on the new server to create the Agent Job.

The obvious question is, instead of using the GUI to script out those users from msdb, is there an actual script i can embed into step one of the job which will do the same thing?

WJHamel
Aged Yak Warrior

USA
645 Posts

Posted - 02/20/2013 :  12:56:38  Show Profile  Reply with Quote
And disregard the portion about adding the IF EXISTS and DROPs. I see that this can also be done automatically in the GUI.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000