public static void RevokeDBPerms(Database sourcedb, Database destdb, string dbusername)
{
var destperms = destdb.EnumDatabasePermissions(dbusername);
var sourceperms = sourcedb.EnumDatabasePermissions(dbusername);
foreach (var perm in destperms)
{
PermissionState permstate = perm.PermissionState;
var sourceperm = sourceperms.Where(p => p.PermissionState == perm.PermissionState && p.PermissionType == perm.PermissionType);
if (sourceperm == null)
{
bool grantwithgrant;
DatabasePermissionSet permset = new DatabasePermissionSet(perm.PermissionType);
if (permstate == PermissionState.GrantWithGrant)
{
grantwithgrant = true;
permstate = PermissionState.Grant;
}
else
{
grantwithgrant = false;
}
destdb.Revoke(permset, dbusername, false, grantwithgrant);
}
}
}
public CommandResult ADD_MSSQL_USER(IConnectToDB _Connect, string UserName, string LoginName, string DatabaseName, string DefaultSchema)
{
CommandResult _result = new CommandResult();
try
{
String connectionString = _Connect.DBConnString;
//TODO: Verify .net Core conversion
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
ServerConnection conn = new ServerConnection();
Server srv = new Server(conn);
Database db = srv.Databases[DatabaseName];
User newUser = new User(db, UserName);
newUser.DefaultSchema = DefaultSchema;
newUser.Login = LoginName;
Thread.Sleep(5000);
newUser.Create();
DatabasePermissionSet perms = new DatabasePermissionSet();
perms.Connect = true;
perms.CreateTable = true;
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Execute = true;
perms.CreateSchema = true;
perms.CreateRole = true;
perms.CreateTable = true;
perms.CreateProcedure = true;
perms.CreateFunction = true;
perms.Control = true;
perms.TakeOwnership = true;
db.Grant(perms, UserName);
_result._Response = "SQL Server UserName " + UserName + " Created for Login " + LoginName + ". The defaultdb is " + DatabaseName;
_result._Successful = true;
}
}
catch (Exception e)
{
_result._Response = e.ToString();
}
_result._EndTime = DateTime.Now;
return(_result);
}
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);
}
}
public static void GrantDBPerms(Database sourcedb, Database destdb, string dbusername)
{
var sourceperms = sourcedb.EnumDatabasePermissions(dbusername);
foreach (var perm in sourceperms)
{
DatabasePermissionSet permset = new DatabasePermissionSet(perm.PermissionType);
PermissionState permstate = perm.PermissionState;
bool grantwithgrant;
if (permstate == PermissionState.GrantWithGrant)
{
grantwithgrant = true;
permstate = PermissionState.Grant;
}
else
{
grantwithgrant = false;
}
destdb.Grant(permset, dbusername, grantwithgrant);
}
}
public string EP_ADD_MSSQL_USER(string ConnAuth, string UserName, string LoginName, string DatabaseName, string DefaultSchema)
{
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["EP_MSSQLRootConn"].ToString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection conn = new ServerConnection(sqlConnection);
Server srv = new Server(conn);
Database db = srv.Databases[DatabaseName];
User newUser = new User(db, UserName);
newUser.DefaultSchema = DefaultSchema;
newUser.Login = LoginName;
Thread.Sleep(5000);
newUser.Create();
DatabasePermissionSet perms = new DatabasePermissionSet();
perms.Connect = true;
perms.CreateTable = true;
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Execute = true;
perms.CreateSchema = true;
perms.CreateRole = true;
perms.CreateTable = true;
perms.CreateProcedure = true;
perms.CreateFunction = true;
perms.Control = true;
perms.TakeOwnership = true;
db.Grant(perms, UserName);
return "SQL Server UserName " + UserName + " Created for Login " + LoginName + ". The defaultdb is " + DatabaseName;
}
catch (Exception e)
{
return e.ToString();
}
}
/// <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();
}
}
