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();
}
}