Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Scripting within an Agent Job

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-02-20 : 11:48:01
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

651 Posts

Posted - 2013-02-20 : 12:56:38
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
   

- Advertisement -