public void CreateIfNotExtists(string serverName, string userName, SecureString password, string databaseName) { var serverConnection = new ServerConnection(serverName, userName, password); var server = new Server(serverName); if (!server.Databases.Contains(databaseName)) { var database = new Microsoft.SqlServer.Management.Smo.Database(server, databaseName); database.Create(); var user = new User(database, userName); user.Create(); var databasePermissionSet = new DatabasePermissionSet(DatabasePermission.CreateType); databasePermissionSet.Add(DatabasePermission.CreateSchema); databasePermissionSet.Add(DatabasePermission.CreateTable); databasePermissionSet.Add(DatabasePermission.CreateFunction); databasePermissionSet.Add(DatabasePermission.CreateProcedure); database.Grant(databasePermissionSet, userName); } }
/// <summary> /// Function that adds a new user with a new database (copied from admin account). /// </summary> public void _AddUserCommand() { Views.Utils.NewLogInDialog newLogInDialog = new Views.Utils.NewLogInDialog("Please enter the new user login data!"); if (newLogInDialog.ShowDialog() == true && newLogInDialog.logIn.Password != "" && newLogInDialog.logIn.Password != "") { // Create database Microsoft.SqlServer.Management.Smo.Database db = new Microsoft.SqlServer.Management.Smo.Database(server, newLogInDialog.logIn.UserName + "_db"); db.Create(); // Create login & user Login login = new Login(server, newLogInDialog.logIn.UserName); login.LoginType = LoginType.SqlLogin; login.Create(newLogInDialog.logIn.Password); User user = new User(db, newLogInDialog.logIn.UserName); user.Login = newLogInDialog.logIn.UserName; user.Create(); trace.Value.TraceEvent(TraceEventType.Information, 0, "Created new User '" + user.Login + "'"); // Creating database permission Sets DatabasePermissionSet databasePermissionSet = new DatabasePermissionSet(); databasePermissionSet.Add(DatabasePermission.Insert); databasePermissionSet.Add(DatabasePermission.Update); databasePermissionSet.Add(DatabasePermission.Select); databasePermissionSet.Add(DatabasePermission.Delete); // Granting Database Permission Sets to Roles db.Grant(databasePermissionSet, newLogInDialog.logIn.UserName); trace.Value.TraceEvent(TraceEventType.Information, 0, "Granted permissions to User '" + user.Login + "'"); // Copy database Microsoft.SqlServer.Management.Smo.Database adminDB = server.Databases[AdminLogIn.UserName + "_db"]; Transfer transfer = new Transfer(adminDB); transfer.CopyAllTables = true; transfer.Options.WithDependencies = true; transfer.Options.DriAll = true; transfer.DestinationDatabase = newLogInDialog.logIn.UserName + "_db"; transfer.DestinationServer = server.Name; transfer.DestinationLoginSecure = false; transfer.DestinationLogin = AdminLogIn.UserName; transfer.DestinationPassword = AdminLogIn.Password; transfer.CopySchema = true; transfer.TransferData(); trace.Value.TraceEvent(TraceEventType.Information, 0, "Copied default database to User '" + user.Login + "'"); FillUserList(); } }