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