private static void AddUserToRole(User user, string role) { if (!(user.IsMember(role))) { user.AddToRole(role); user.Alter(); } }
private static void AddUserToRoles(User user, params string[] roles) { if (roles.IsNullOrEmpty()) return; foreach (var role in roles) { if (!(user.IsMember(role))) { user.AddToRole(role); } } user.Alter(); }
static void Main(string[] args) { //Parse command line parameters Dictionary<string, string> param = new Dictionary<string, string>(); foreach (var v in args) { int p = v.IndexOf("="); if (p > 0) { param.Add(v.Substring(0, p), v.Substring(p + 1, v.Length - p - 1)); } } if (param.Count == 0) { //just show help ShowUsage(); WriteLine("Press any key to close"); Console.ReadKey(); Environment.Exit(0); } string server; string database; string newDatabase = null; string user = null; string password = null; string databaseFileName = ""; if (!param.TryGetValue("server", out server)) server = "."; if (!param.TryGetValue("db", out database)) WarnAndExit("\"db\" command line parameter was not specified"); if (!param.TryGetValue("newdb", out newDatabase)) newDatabase = null; if (!param.TryGetValue("user", out user)) user = null; if (!param.TryGetValue("password", out password)) password = null; if (!param.TryGetValue("filename", out databaseFileName)) databaseFileName = null; if (string.IsNullOrEmpty(newDatabase)) newDatabase = database + "_Unicode"; //Connect to the specified server ServerConnection connection = new ServerConnection(server); Database db = null; Server svr = null; if (string.IsNullOrEmpty(user)) { connection.LoginSecure = true; //Windows authentication } else { connection.LoginSecure = false; connection.Login = user; connection.Password = password; } try { svr = new Server(connection); db = svr.Databases[database]; } catch (Exception e) { WarnAndExit(string.Format("Could not connect to server \"{0}\": {1}", server, e.Message)); } if (db == null) WarnAndExit(string.Format("Database \"{0}\" does not exist on the server \"{1}\"", database, server)); //create new database Database newDB = svr.Databases[newDatabase]; if (newDB != null) { try { Notify(string.Format("Dropping the existing \"{0}\" database", newDatabase)); svr.KillAllProcesses(newDatabase); svr.KillDatabase(newDatabase); } catch (Exception e) { ReportException(e); } newDB = svr.Databases[newDatabase]; } if (newDB != null) { WriteLine(string.Format("Target database \"{0}\" already exists. All existing data will be deleted", newDatabase), OutputKind.Warning); //store views/tables/triggers in a list List<View> oldViews = new List<View>(); foreach (View v in newDB.Views) if (!v.IsSystemObject) oldViews.Add(v); List<Table> oldTables = new List<Table>(); foreach (Table t in newDB.Tables) if (!t.IsSystemObject) oldTables.Add(t); List<Trigger> oldTriggers = new List<Trigger>(); foreach (Trigger t in newDB.Triggers) if (!t.IsSystemObject) oldTriggers.Add(t); //delete 'em foreach (Trigger t in oldTriggers) t.Drop(); foreach (View v in oldViews) v.Drop(); foreach (Table t in oldTables) t.Drop(); } else { WriteLine(string.Format("Creating new database \"{0}\" ", newDatabase), OutputKind.Info); newDB = new Database(svr, newDatabase); newDB.Collation = db.Collation; newDB.DefaultSchema = db.DefaultSchema; //should it be "sysdba"? FileGroup dbFG = new FileGroup(newDB, "PRIMARY"); newDB.FileGroups.Add(dbFG); //Now add primary db file to file group if (string.IsNullOrEmpty(databaseFileName)) { string oldDatabaseFilename = db.FileGroups[0].Files[0].FileName; string directory = Path.GetDirectoryName(oldDatabaseFilename); databaseFileName = directory + @"\" + newDatabase + ".mdf"; } DataFile df1 = new DataFile(dbFG, "SalesLogix_Data"); dbFG.Files.Add(df1); df1.FileName = databaseFileName; df1.Size = 10.0*1024.0; df1.GrowthType = FileGrowthType.Percent; df1.Growth = 25.0; try { newDB.Create(); } catch (Exception e) { WriteLine(string.Format("Could not create database \"{0}\"", newDatabase), OutputKind.Error); ReportException(e); WarnAndExit(""); } } //copy the users foreach (User oldUser in db.Users) { User newUser = newDB.Users[oldUser.Name]; if (newUser == null) { Notify("Processing user " + oldUser.Name); try { newUser = new User(newDB, oldUser.Name); newUser.DefaultSchema = oldUser.DefaultSchema; newUser.UserType = oldUser.UserType; newUser.Login = oldUser.Login; newDB.Users.Add(newUser); newUser.Create(); StringCollection roles = oldUser.EnumRoles(); foreach (string role in roles) newUser.AddToRole(role); newUser.Alter(); } catch (Exception e) { ReportException(e); } } } //copy schemas foreach (Schema oldSchema in db.Schemas) { Schema newSchema = newDB.Schemas[oldSchema.Name]; if (newSchema == null) { Notify("Processing schema " + oldSchema.Name); try { newSchema = new Schema(newDB, oldSchema.Name); newSchema.Owner = oldSchema.Owner; newDB.Schemas.Add(newSchema); newSchema.Create(); } catch (Exception e) { ReportException(e); } } } //copy datatype foreach (UserDefinedDataType oldType in db.UserDefinedDataTypes) { UserDefinedDataType newType = newDB.UserDefinedDataTypes[string.Format("[{0}].[{1}]", oldType.Owner, oldType.Name)]; if (newType == null) newType = newDB.UserDefinedDataTypes[oldType.Name]; if (newType == null) { Notify("Processing user data type " + oldType.Name); try { newType = new UserDefinedDataType(newDB, oldType.Name, oldType.Schema); newType.Owner = oldType.Owner; //adjust the type correctly string systemType = oldType.SystemType; switch (systemType.ToUpper()) { case "VARCHAR": { systemType = "NVARCHAR"; break; } case "CHAR": { systemType = "NCHAR"; break; } case "TEXT": { systemType = "NTEXT"; break; } } newType.SystemType = systemType; newType.Length = oldType.Length; newType.Nullable = oldType.Nullable; newType.Default = oldType.Default; newType.DefaultSchema = oldType.DefaultSchema; newType.NumericPrecision = oldType.NumericPrecision; newType.NumericScale = oldType.NumericScale; newType.Rule = oldType.Rule; newType.RuleSchema = oldType.RuleSchema; newDB.UserDefinedDataTypes.Add(newType); newType.Create(); } catch (Exception e) { ReportException(e); } } } //copy the schema StringBuilder sb = new StringBuilder(); ScriptingOptions options = new ScriptingOptions(); options.ClusteredIndexes = true; options.Default = true; options.DriAll = true; options.Indexes = true; options.IncludeHeaders = true; options.DriAllConstraints = true; options.DriIndexes = true; options.FullTextIndexes = true; options.ExtendedProperties = true; options.NoCollation = true; //we will convert TEXT to NTEXT anyway options.NoCommandTerminator = false; //we do need this - otherwise CREATE VIEW has problems options.ClusteredIndexes = true; options.NonClusteredIndexes = true; options.SchemaQualify = true; options.ScriptSchema = true; options.SchemaQualifyForeignKeysReferences = true; options.IncludeDatabaseContext = false; //since we wil be executing in a different DB context options.Triggers = true; //copy tables foreach (Table table in db.Tables) { if (!table.IsSystemObject) { Table oldTable = newDB.Tables[table.Name]; if (oldTable != null) { try { Notify("Dropping existing table " + oldTable.Name); oldTable.Drop(); } catch (Exception e) { ReportException(e); } } sb.Length = 0; //don't script inserts - we run out of memory options.ScriptData = false; StringCollection coll = table.Script(options); foreach (string str in coll) { sb.AppendLine(str); } Notify("Creating table " + table.Name); string sql = ChangeAnsiToUnicode(sb.ToString()); try { newDB.ExecuteNonQuery(sql); } catch (Exception e) { ReportException(e, sql); } var x = Missing.Value; } } //copy views foreach (View view in db.Views) { if (!view.IsSystemObject) { View oldView = newDB.Views[view.Name]; if (oldView != null) { try { Notify("Dropping existing view " + oldView.Name); oldView.Drop(); } catch (Exception e) { ReportException(e); } } sb.Length = 0; StringCollection coll = view.Script(options); foreach (string str in coll) { string line = str; //make it a local avriable since we change it below if (line.Trim().StartsWith("create view", true, CultureInfo.CurrentCulture)) { //'CREATE VIEW' must be the first statement in a query batch. sb.AppendLine("GO"); //make sure we have the right schema when we call CREATE VIEW if ((line.ToUpper().IndexOf(".[" + view.Name.ToUpper()) < 0) && //if there is no preceeing "]." before [viewname], the schema is not specified (line.ToUpper().IndexOf("SYSDBA." + view.Name.ToUpper()) < 0)) { int p = line.ToUpper().IndexOf(string.Format("[{0}]", view.Name.ToUpper())); if (p >= 0) { line = line.Replace(string.Format("[{0}]", view.Name), string.Format("[sysdba].[{0}]", view.Name)); } else { p = line.ToUpper().IndexOf(string.Format("CREATE VIEW {0}", view.Name.ToUpper())); if (p >= 0) { line = line.ToUpper().Replace(string.Format("CREATE VIEW {0}", view.Name), string.Format("CREATE VIEW [sysdba].[{0}]", view.Name)); } } } } sb.AppendLine(line); } try { Notify("Creating view " + view.Name); newDB.ExecuteNonQuery(sb.ToString()); } catch (Exception e) { ReportException(e, sb.ToString()); } } } //now stuff existing data into the new db string connectionString = ""; if (string.IsNullOrEmpty(user)) { //Windows Auth connectionString = string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={0};Data Source={1}", newDatabase, server); } else { //SQL Auth connectionString = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3}", password, user, newDatabase, server); } SqlConnection newConnection = new SqlConnection(connectionString); newConnection.Open(); SqlBulkCopy bulkCopy = new SqlBulkCopy(newConnection); bulkCopy.BulkCopyTimeout = int.MaxValue; foreach (Table table in db.Tables) { if (!table.IsSystemObject) { try { Notify("Copying data to table " + table.Name); string sql = string.Format("SELECT * FROM {0}.{1}", table.Owner, table.Name); using (DataSet dataset = db.ExecuteWithResults(sql)) { bulkCopy.DestinationTableName = string.Format("{0}.{1}", table.Owner, table.Name); if (dataset.Tables.Count > 0) { DataTable t = dataset.Tables[0]; bulkCopy.WriteToServer(t); } } } catch (Exception e) { ReportException(e); } } GC.Collect(); //we can use lots of memory above! } //make sure the DB is marked as Unicode string markAsUnicodeSQL = "UPDATE SYSDBA.SYSTEMINFO SET UNICODE = 'T' "; try { Notify("Marking the new database as Unicode enabled"); newDB.ExecuteNonQuery(markAsUnicodeSQL); } catch (Exception e) { ReportException(e, markAsUnicodeSQL); } //convert the virtual file system Notify("Updating VIRTUALFILESYSTEM table..."); try { //we need a conneciton for updates since SMO won't let us use parameters for updates //when we update large binary fields (VIRTUALFILESYSTEM.ITEMDATA) string updateConnectionString = ""; if (string.IsNullOrEmpty(user)) { //Windows Auth updateConnectionString = string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={0};Data Source={1}", newDatabase, server); } else { //SQL Auth updateConnectionString = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3}", password, user, newDatabase, server); } SqlConnection updateConnection = new SqlConnection(updateConnectionString); updateConnection.Open(); string vfsSql = "SELECT VIRTUALFILESYSTEMID, ITEMNAME, ITEMDATA, ISCOMPRESSED FROM SYSDBA.VIRTUALFILESYSTEM WHERE ITEMNAME LIKE '%.entity.xml' "; using (DataSet vfsDataset = newDB.ExecuteWithResults(vfsSql)) { if (vfsDataset.Tables.Count > 0) { DataTable t = vfsDataset.Tables[0]; foreach (DataRow row in t.Rows) { string itemName = (string) row["ITEMNAME"]; string[] arrItemName = itemName.Split(new string[] {"."}, StringSplitOptions.None); //Name.TABLE.entity.xml if (arrItemName.Length > 2) { string entityName = arrItemName[0]; string tableName = arrItemName[1]; Notify(string.Format("Updating entity {0} (Table {1})", entityName, tableName)); try { Table table = null; foreach (Table currTable in newDB.Tables) { if ((String.Compare(currTable.Name, tableName, true) == 0) && (String.Compare(currTable.Owner, "sysdba", true) == 0)) { table = currTable; break; } } if (table != null) { object ItemData = row["ITEMDATA"]; //we expect an array if (ItemData != null) { object strIsCompressed = row["ISCOMPRESSED"]; Boolean bIsCompressed = (strIsCompressed != null) && (string.Compare((string) strIsCompressed, "T", true) == 0); using (var memoryStream = UnpackItemData((byte[]) ItemData, bIsCompressed)) { XmlDocument xmlEntityDocument = new XmlDocument(); memoryStream.Position = 0; xmlEntityDocument.Load(memoryStream); Boolean XmlModified = false; foreach (Column column in table.Columns) { //is this a string derived type? SqlDataType dataType = column.DataType.SqlDataType; if (column.DataType.SqlDataType == SqlDataType.UserDefinedDataType) { UserDefinedDataType colType = newDB.UserDefinedDataTypes[string.Format("[{0}].[{1}]", column.DataType.Schema, column.DataType.Name)]; if (colType == null) colType = newDB.UserDefinedDataTypes[column.DataType.Name]; if (colType != null) { switch (colType.SystemType.ToUpper()) { case "NVARCHAR": { dataType = SqlDataType.NVarChar; break; } case "NCHAR": { dataType = SqlDataType.NChar; break; } case "NTEXT": { dataType = SqlDataType.NText; break; } } } } if ((dataType == SqlDataType.NChar) || (dataType == SqlDataType.NText) || (dataType == SqlDataType.NVarChar) || (dataType == SqlDataType.NVarCharMax) ) { XmlModified |= ModifyEntityColumn(xmlEntityDocument, column.Name); } } if (XmlModified) { //now save it memoryStream.SetLength(0); xmlEntityDocument.Save(memoryStream); memoryStream.Position = 0; byte[] newData = PackItemData(memoryStream, true, ref bIsCompressed); string updateSql = string.Format( "UPDATE sysdba.VIRTUALFILESYSTEM SET ITEMDATA = @DATA, ISCOMPRESSED = @COMPRESSED WHERE VIRTUALFILESYSTEMID = '{0}'", row["VIRTUALFILESYSTEMID"] ); SqlParameter parameter; SqlCommand command = new SqlCommand(updateSql, updateConnection); parameter = command.Parameters.Add("@DATA", SqlDbType.Image); parameter.Value = newData; parameter = command.Parameters.Add("@COMPRESSED", SqlDbType.NVarChar); parameter.Value = bIsCompressed ? "T" : "F"; command.ExecuteNonQuery(); } } } } } catch (Exception e) { ReportException(e); } } } } } } catch (Exception e) { ReportException(e); } GC.Collect(); }
public void CreateUser(string name, string database, string defaultSchema, string login, string[] roles) { Database db = _server.Databases[database]; User user = new User(db, name) {Login = login, DefaultSchema = defaultSchema}; user.Create(); for (int i = 0; i < roles.Length; i++) user.AddToRole(roles[i]); user.Alter(); }