private void copyAlerts(Server destserver)
{
foreach (Alert alert in sourceserver.JobServer.Alerts)
{
string alertname = alert.Name;
ItemToCopy item = itemsToCopy.Find(x => x.Name == alertname);
if (item.IsChecked)
{
if (DBChecks.AlertExists(destserver, alertname))
{
showOutput.displayOutput(string.Format("Alert {0} already exists in destination. Skipping.", alertname));
continue;
}
if (DBChecks.CategoryExists(destserver, alert.CategoryName))
{
showOutput.displayOutput(string.Format("Category {0} does not exist. Skipping copy of alert {1}.", alert.CategoryName, alertname));
continue;
}
try
{
StringCollection sql = alert.Script();
destserver.ConnectionContext.ExecuteNonQuery(sql);
destserver.JobServer.Refresh();
showOutput.displayOutput(string.Format("Copied alert {0} to {1}", alertname, destserver.Name));
}
catch (Exception ex)
{
showOutput.displayOutput(ex.Message);
continue;
}
}
}
}
private void copyOperators(Server destserver)
{
foreach (Operator op in sourceserver.JobServer.Operators)
{
string opname = op.Name;
ItemToCopy item = itemsToCopy.Find(x => x.Name == opname);
if (item.IsChecked)
{
if (DBChecks.OperatorExists(destserver, opname))
{
showOutput.displayOutput(string.Format("Operator {0} already exists in destination. Skipping.", opname));
continue;
}
try
{
StringCollection sql = op.Script();
destserver.ConnectionContext.ExecuteNonQuery(sql);
destserver.JobServer.Refresh();
showOutput.displayOutput(string.Format("Copied operator {0} to {1}", opname, destserver.Name));
}
catch (Exception ex)
{
showOutput.displayOutput(ex.Message);
continue;
}
}
}
}
private void copyCategories(Server destserver)
{
foreach (JobCategory cat in sourceserver.JobServer.JobCategories)
{
string categoryname = cat.Name;
ItemToCopy item = itemsToCopy.Find(x => x.Name == categoryname);
if (item.IsChecked)
{
if (DBChecks.CategoryExists(destserver, categoryname))
{
showOutput.displayOutput(string.Format("Category {0} already exists in destination. Skipping.", categoryname));
continue;
}
try
{
JobCategory destcat = new JobCategory();
destcat.Name = categoryname;
destcat.Parent = destserver.JobServer;
destcat.CategoryType = cat.CategoryType;
destcat.Create();
}
catch (Exception ex)
{
showOutput.displayOutput(ex.Message);
continue;
}
showOutput.displayOutput(string.Format("Copied category {0} to destination", categoryname));
}
}
}
private void findOrphanUsers()
{
listOrphanUsers.Clear();
listOrphanUsers.View = System.Windows.Forms.View.Details;
listOrphanUsers.Columns.Add("Database Name");
listOrphanUsers.Columns.Add("User");
listOrphanUsers.Columns.Add("Login Type");
listOrphanUsers.Columns.Add("Matching Login Name");
foreach (Database db in sourceServer.Databases)
{
if (!string.IsNullOrEmpty(databaseName) && db.Name.ToLower() != databaseName.ToLower())
{
continue;
}
foreach (User dbUser in db.Users)
{
if (!dbUser.IsSystemObject && dbUser.Login == string.Empty)
{
string matchedlogin = "";
if (DBChecks.LoginExists(sourceServer, dbUser.Name))
{
matchedlogin = dbUser.Name;
}
listOrphanUsers.Items.Add(new ListViewItem(new string[] { db.Name, dbUser.Name, dbUser.LoginType.ToString(), matchedlogin }));
}
}
}
listOrphanUsers.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
listOrphanUsers.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
private void btnFix_Click(object sender, EventArgs e)
{
foreach (ListViewItem item in listOrphanUsers.CheckedItems)
{
try
{
if (!string.IsNullOrEmpty(item.SubItems[3].Text))
{
Database db = sourceServer.Databases[item.Text];
db.ExecuteNonQuery("sp_change_users_login 'Update_one' ,'" + item.SubItems[1].Text + "', '" + item.SubItems[3].Text + "'");
}
else
{
if (!DBChecks.LoginExists(sourceServer, item.SubItems[3].Text))
{
Database db = sourceServer.Databases[item.Text];
db.ExecuteNonQuery("sp_change_users_login 'Auto_Fix' ,'" + item.SubItems[1].Text + "', NULL, 'P@ssw0rd'");
MessageBox.Show("Login [" + item.SubItems[1].Text + "] created with a password of 'P@ssw0rd'");
}
else
{
Database db = sourceServer.Databases[item.Text];
db.ExecuteNonQuery("sp_change_users_login 'Update_one' ,'" + item.SubItems[1].Text + "', '" + item.SubItems[3].Text + "'");
}
}
listOrphanUsers.Items.Remove(item);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
continue;
}
}
listOrphanUsers.Refresh();
}
private void copyJobs(Server destserver, bool disableonsource, bool disableondest, bool dropdest)
{
JobCollection destjobs = destserver.JobServer.Jobs;
foreach (Job job in sourceserver.JobServer.Jobs)
{
string jobname = job.Name;
ItemToCopy item = itemsToCopy.Find(x => x.Name == jobname);
if (item.IsChecked)
{
if (!DBChecks.LoginExists(destserver, job.OwnerLoginName))
{
showOutput.displayOutput(string.Format("[Job: {0}] Owner {1} doesn't exist ion destination. Skipping. ", jobname, job.OwnerLoginName));
continue;
}
foreach (JobStep jobstep in job.JobSteps)
{
if (!DBChecks.DatabaseExists(destserver, jobstep.DatabaseName))
{
showOutput.displayOutput(string.Format("[Job: {0}] Database {1} doesn't exist on destination. Skipping. ", jobname, jobstep.DatabaseName));
continue;
}
if (!string.IsNullOrEmpty(jobstep.ProxyName) && !DBChecks.ProxyExists(destserver, jobstep.ProxyName))
{
showOutput.displayOutput(string.Format("[Job: {0}] Proxy {1} doesn't exist on destination. Skipping. ", jobname, jobstep.ProxyName));
continue;
}
}
if (!string.IsNullOrEmpty(job.OperatorToEmail) && !DBChecks.OperatorExists(destserver, job.OperatorToEmail))
{
showOutput.displayOutput(string.Format("[Job: {0}] Operator {1} doesn't exist on destination. Skipping. ", jobname, job.OperatorToEmail));
continue;
}
if (!string.IsNullOrEmpty(job.OperatorToNetSend) && !DBChecks.OperatorExists(destserver, job.OperatorToNetSend))
{
showOutput.displayOutput(string.Format("[Job: {0}] Operator {1} doesn't exist on destination. Skipping. ", jobname, job.OperatorToNetSend));
continue;
}
if (!string.IsNullOrEmpty(job.OperatorToPage) && !DBChecks.OperatorExists(destserver, job.OperatorToPage))
{
showOutput.displayOutput(string.Format("[Job: {0}] Operator {1} doesn't exist on destination. Skipping. ", jobname, job.OperatorToPage));
continue;
}
if (DBChecks.JobExists(destserver, jobname))
{
if (!dropdest)
{
showOutput.displayOutput(string.Format("Job: {0} already exists on destination server. Skipping. ", jobname));
continue;
}
else
{
try
{
destserver.JobServer.Jobs[jobname].Drop();
destserver.JobServer.Refresh();
}
catch (Exception ex)
{
showOutput.displayOutput(ex.Message);
continue;
}
}
}
try
{
StringCollection sql = job.Script();
for (int i = 0; i < sql.Count; i++)
{
sql[i] = sql[i].Replace(sourceserver.Name, destserver.Name);
}
destserver.ConnectionContext.ExecuteNonQuery(sql);
if (disableondest)
{
DBFunctions.ChangeJobStatus(destserver, jobname, false);
}
if (disableonsource)
{
DBFunctions.ChangeJobStatus(sourceserver, jobname, false);
}
showOutput.displayOutput(string.Format("Copied job {0} to {1}", jobname, destserver.Name));
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Failed to copy job {0} to {1}", jobname, destserver.Name));
showOutput.displayOutput(ex.Message);
continue;
}
}
}
}
private void syncDatabasePerms(Login sourcelogin, Login destlogin, Server sourceserver, Server destserver)
{
// Remove user from destination if it does not exist on source
if (destlogin.EnumDatabaseMappings() != null)
{
foreach (DatabaseMapping dbmap in destlogin.EnumDatabaseMappings())
{
string dbname = dbmap.DBName;
Database destdb = destserver.Databases[dbname];
Database sourcedb = sourceserver.Databases[dbname];
string dbusername = dbmap.UserName;
string dbloginname = dbmap.LoginName;
if (DBChecks.DatabaseExists(sourceserver, destdb.Name) &&
!DBChecks.DatabaseUserExists(sourcedb, dbusername) && DBChecks.DatabaseUserExists(destdb, dbusername))
{
try
{
DBFunctions.DropDBUser(sourcedb, destdb, dbusername);
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Failed to drop user {0} From {1} on destination.", dbusername, dbname), true);
showOutput.displayOutput(ex.Message);
}
try
{
DBFunctions.RevokeDBPerms(sourcedb, destdb, dbusername);
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Failed to revoke permission for user {0} on {1}.", dbusername, dbname), true);
showOutput.displayOutput(ex.Message, true);
}
}
}
}
// Add the database mappings and permissions
{
if (sourcelogin.EnumDatabaseMappings() != null)
{
foreach (DatabaseMapping dbmap in sourcelogin.EnumDatabaseMappings())
{
string dbname = dbmap.DBName;
Database destdb = destserver.Databases[dbname];
Database sourcedb = sourceserver.Databases[dbname];
string dbusername = dbmap.UserName;
string dbloginname = dbmap.LoginName;
// Only if database exists on destination and its status is normal
if (DBChecks.DatabaseExists(destserver, sourcedb.Name) &&
DBChecks.LoginExists(destserver, dbloginname) && !DBChecks.DatabaseUserExists(destdb, dbusername) &&
destdb.Status == DatabaseStatus.Normal)
{
// Add DB User
try
{
DBFunctions.AddDBUser(destdb, dbusername);
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Failed to add user {0} to database {1}", dbusername, dbname), true);
showOutput.displayOutput(ex.Message, true);
}
//Change the owner
if (sourcedb.Owner == dbusername)
{
DBFunctions.ChangeDbOwner(destserver, null, dbusername, dbname);
}
//Map the roles
try
{
DBFunctions.AddUserToDBRoles(sourcedb, destdb, dbusername);
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Error adding user {0} to role on database {1}", dbusername, dbname), true);
showOutput.displayOutput(ex.Message, true);
}
//Map permissions
try
{
DBFunctions.GrantDBPerms(sourcedb, destdb, dbusername);
}
catch (Exception ex)
{
showOutput.displayOutput(string.Format("Error granting permission for user {0} on database {1}", dbusername, dbname), true);
showOutput.displayOutput(ex.Message, true);
}
}
showOutput.displayOutput(string.Format("Database permissions synced for user {0} on database {1}", dbusername, dbname));
}
}
}
}
