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