private void AddUserToDatabase(SqlConnection connection, OdsSqlCredential credential) { //Note: DDL statements cannot be parameterized, hence the string building and manual quote replacement //This process will get data from a trusted user for one-time setup, so the risk is largely mitigated var quotedUserName = EscapeSingleQuotes(credential.UserName); var sql = $@"IF DATABASE_PRINCIPAL_ID('{quotedUserName}') IS NULL BEGIN CREATE USER [{credential.UserName}] FOR LOGIN [{credential.UserName}] WITH DEFAULT_SCHEMA=[dbo] END"; _rawSqlConnectionService.ExecuteDdl(connection, sql); }
private void CreateLogin(SqlConnection connection, OdsSqlCredential credential) { //Note: DDL statements cannot be parameterized, hence the string building and manual quote replacement //This process will get data from a trusted user for one-time setup, so the risk is largely mitigated var quotedUserName = EscapeSingleQuotes(credential.UserName); var password = EscapeSingleQuotes(credential.Password); var sql = $@"IF NOT EXISTS(SELECT name FROM sys.sql_logins WHERE name='{quotedUserName}') BEGIN CREATE LOGIN [{credential.UserName}] WITH PASSWORD = '******' END"; _rawSqlConnectionService.ExecuteDdl(connection, sql); }
private void RemoveUserFromRole(SqlConnection connection, OdsSqlCredential credential, params ISqlRole[] roles) { foreach (var role in roles) { var sql = "sp_droprolemember"; using (var command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@rolename", role.RoleName); command.Parameters.AddWithValue("@membername", credential.UserName); command.ExecuteNonQuery(); } } }
private void ValidateCredentials(OdsSqlCredential actual, OdsSqlCredential expected) { actual.UserName.ShouldBe(expected.UserName); actual.Password.ShouldBe(expected.Password); }