private static void CreateLoginIfNotExists(Server server, Database database) { Login login = null; if (!server.LoginExists(Configuration.LoginName)) { Console.WriteLine("Creating Login {0}", Configuration.LoginName); login = server.CreateSqlLogin(Configuration.LoginName, Configuration.LoginPassword, Configuration.DatabaseName); } else { login = server.Logins[Configuration.LoginName]; } if (!database.UserExists(Configuration.LoginName)) { Console.WriteLine("Creating User {0} for Database {1}", login.Name, database.Name); var user = new User(database, Configuration.LoginName); user.Login = login.Name; user.Create(); user.AddToRole("db_owner"); } else { ConsoleHelper.WriteLine(ConsoleColor.Yellow, string.Format("User {0} already exists in database {1}, creation skiped", Configuration.LoginName, Configuration.DatabaseName)); } }
public static void UpdateDataSet(this MSMO.User user, Data.ApplicationDataSet.DBUsersRow dataRow) { try { //dataRow.X = user.X; } catch (Exception ex) { VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7); // TODO(crhodes): // Wrap anything above that throws an exception that we want to ignore, // e.g. property not available because of SQL Edition. } }
public static void UpdateDataSet(this MSMO.User user, Data.ApplicationDataSet.DBUsersRow userRow) { try { //instanceRow.ServiceName = server.ServiceName; } catch (Exception ex) { // TODO(crhodes): Need to wrap anything above that throws an exception // that we want to ignore, e.g. property not available because of // SQL Edition. PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2); throw ex; } }
private static void Update(MSMO.User user, SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow) { try { user.UpdateDataSet(dataRow); UpdateDatabaseWithSnapShot(dataRow, ""); } catch (Exception ex) { VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6); UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256)); } }
private static SQLInformation.Data.ApplicationDataSet.DBUsersRow Add(MSMO.User user, Guid databaseID, string databaseName) { SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow = null; try { dataRow = Common.ApplicationDataSet.DBUsers.NewDBUsersRow(); dataRow.ID = Guid.NewGuid(); dataRow.Database_ID = databaseID; dataRow.Name_Database = databaseName; dataRow.IsSystemObject = user.IsSystemObject; dataRow.Name_User = user.Name; dataRow.CreateDate = user.CreateDate; dataRow.DateLastModified = user.DateLastModified; dataRow.Login = user.Login; dataRow.LoginType = user.LoginType.ToString(); dataRow.SnapShotDate = DateTime.Now; dataRow.SnapShotError = ""; Common.ApplicationDataSet.DBUsers.AddDBUsersRow(dataRow); Common.ApplicationDataSet.DBUsersTA.Update(Common.ApplicationDataSet.DBUsers); } catch (Exception ex) { VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5); // TODO(crhodes): Need to wrap anything above that throws an exception // that we want to ignore, e.g. property not available because of // SQL Edition. UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256)); } return(dataRow); }
public User(Smo.User user, Server server) { _user = user; _server = server; }
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose) { // Store extracted scripts. Each extract may include multiple scripts. StringCollection OutputScripts = new StringCollection(); string FinalScript = String.Empty; switch (oname.ObjectType) { case SQLObjectType.Table: Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema]; if (scriptTable != null) { StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked String TableScript = String.Empty; // Stores individual script for output collection. ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.Statistics = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.DriAllConstraints = true; scriptOptions.WithDependencies = false; // Get table and related scripts CheckScripts = scriptTable.Script(scriptOptions); // Check scripts so we can remove invalide SQL 2012 column store options from the script. // (Why doesn't the target server version remove this? // This is a crappy place to do this, and it's version specific. // Need to implement the new versioning code to check target model. foreach (string CheckCCI in CheckScripts) { if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE")) { TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", ""); } else { TableScript = CheckCCI; } // Add the script into the OutputScripts collection. OutputScripts.Add(TableScript); } } break; case SQLObjectType.View: Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema]; if (scriptView != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.WithDependencies = false; // Must specify tables seperatly, but safer to do so // to avoid having duplicate table names in the model. OutputScripts = scriptView.Script(scriptOptions); } break; case SQLObjectType.StoredProcedure: Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema]; if (scriptStoredProcedure != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptStoredProcedure.Script(scriptOptions); } break; case SQLObjectType.PartitionScheme: { Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name]; if (scriptPScheme != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPScheme.Script(scriptOptions); } } break; case SQLObjectType.PartitionFunction: { Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name]; if (scriptPFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPFunction.Script(scriptOptions); } } break; case SQLObjectType.Schema: { Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name]; if (scriptSchema != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; scriptOptions.ScriptOwner = true; // This includes the "with authorize" part. OutputScripts = scriptSchema.Script(scriptOptions); } } break; case SQLObjectType.FileGroup: { Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name]; if (scriptFG != null) { // Create manual script for FileGroups OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name); } } break; case SQLObjectType.User: { Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name]; if (scriptUser != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptUser.Script(scriptOptions); } } break; case SQLObjectType.Function: Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema]; if (userDefinedFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = userDefinedFunction.Script(scriptOptions); } break; } if (OutputScripts.Count > 0) { Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); foreach (string script in OutputScripts) { // Add the script to the script collection. FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine; } } else { Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); FinalScript = String.Empty; } if (FinalScript != String.Empty) { SQLScriptsCollection.Scripts.Add(FinalScript); } else { SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); } // Print script(s) if verbose is on. if (Verbose) { Console.WriteLine(FinalScript); } }
protected override void DoWork(object state) { var mainCancellationTokenSource = new CancellationToken(); try { var tasks = new List <Task>(); var deploymentJobs = _deploymentJobService.Get(p => p.Server.Code == ServerId && p.JobType == (int)JobType.Database && (p.IsCopySourceDone ?? false) && (p.IsStart ?? false) && !(p.IsDone ?? false)) .OrderBy(p => p.FailCount) .Take(2).ToList(); var listJobUpdated = new List <DeploymentJob>(); foreach (var job in deploymentJobs) { var localJob = job; var t = Task.Factory.StartNew(() => { var dbName = localJob.Configuration.DatabaseName; var loginName = localJob.Configuration.DatabaseUsername; var loginPassword = EncryptHelper.Decrypt(localJob.Configuration.DatabasePassword); var pass = PasswordHelper.HashString("123456", "camino"); var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString); var file = new FileInfo(localJob.Configuration.SqlScriptPath); var script = file.OpenText().ReadToEnd(); script = script.Replace("{{DATABASE_NAME}}", dbName) .Replace("{{FRANCHISEE_ADMIN_NAME}}", "camino") .Replace("{{FRANCHISEE_ADMIN_PASSWORD}}", pass) .Replace("{{FRANCHISEE_ADMIN_EMAIL}}", "*****@*****.**"); var server = new Server(new ServerConnection(conn)); try { server.ConnectionContext.ExecuteNonQuery(script); using (var scope = new TransactionScope()) { var db = server.Databases[dbName]; var login = server.Logins[loginName]; if (login == null) { // Creating Logins login = new Login(server, loginName) { LoginType = LoginType.SqlLogin, PasswordPolicyEnforced = false }; login.Create(loginPassword); login.DefaultDatabase = dbName; login.Alter(); } // Creating Users in the database for the logins created if (db.Users[loginName] != null) { return; } var dbUser = new User(db, loginName) { UserType = UserType.SqlLogin, Login = login.Name, }; dbUser.Create(); dbUser.AddToRole("db_owner"); localJob.IsDone = true; scope.Complete(); } } catch (Exception exception) { server = new Server(new ServerConnection(conn)); server.KillAllProcesses(dbName); server.KillDatabase(dbName); var db = server.Databases[dbName]; db.Drop(); localJob.IsDone = false; localJob.FailCount = (localJob.FailCount ?? 0) + 1; _diagnosticService.Error(exception); } listJobUpdated.Add(localJob); }, mainCancellationTokenSource); tasks.Add(t); } Task.WaitAll(tasks.ToArray(), mainCancellationTokenSource); _deploymentJobService.UpdateListJobs(listJobUpdated); } catch (Exception ex) { _diagnosticService.Error(ex); } }
private static SQLInformation.Data.ApplicationDataSet.DBUsersRow GetInfoFromSMO(MSMO.User user, Guid databaseID, string databaseName) { #if TRACE long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2); #endif SQLInformation.Data.ApplicationDataSet.DBUsersRow dataRow = null; try { var dbs = from tb in Common.ApplicationDataSet.DBUsers where tb.Database_ID == databaseID select tb; var dbs2 = from db2 in dbs where db2.Name_User == user.Name select db2; if (dbs2.Count() > 0) { dataRow = dbs2.First(); Update(user, dataRow); } else { dataRow = Add(user, databaseID, databaseName); } } catch (Exception ex) { VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3); } #if TRACE VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks); #endif return(dataRow); }