public OperationResult DeleteRoleForUser(string serverAddress, string username,
string password, string catalog, string loginUsername, string roleName)
{
if (string.IsNullOrWhiteSpace(serverAddress))
{
throw new ArgumentNullException(nameof(serverAddress));
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException(nameof(username));
}
if (string.IsNullOrWhiteSpace(password))
{
throw new ArgumentNullException(nameof(password));
}
if (string.IsNullOrWhiteSpace(loginUsername))
{
throw new ArgumentNullException(nameof(loginUsername));
}
var hasInvalidSymbols = SqlStringValidator.HasInvalidSymbols(loginUsername);
if (hasInvalidSymbols)
{
return(new OperationResult(false, "The username contains illegal characters"));
}
var isInjectionAttempt = SqlStringValidator.HasInjectionCommand(loginUsername);
if (isInjectionAttempt)
{
return(new OperationResult(false, "The username contains illegal characters"));
}
var isValidRole = SqlStringValidator.IsValidRole(roleName);
if (!isValidRole)
{
return(new OperationResult(false, "The given role does not exist in SQL"));
}
var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password, catalog);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var hasCatalogUser = HasUserInDatabase(loginUsername, connection);
if (!hasCatalogUser)
{
return(new OperationResult(false, "The given user does not have this role in the target database"));
}
using (var command = new SqlCommand("sp_droprolemember", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters
.Add("@membername", loginUsername)
.Add("@rolename", roleName);
using (var _ = command.ExecuteReader())
{
// Nothing much to do here
}
}
const string ROLES_IN_DATABASE_SQL =
"SELECT COUNT(*) FROM sys.database_role_members dbrolemembers "
+ "INNER JOIN sys.database_principals dbprincipals ON dbprincipals.principal_id = dbrolemembers.role_principal_id "
+ "INNER JOIN sys.database_principals dbuserprincipals ON dbuserprincipals.principal_id = dbrolemembers.member_principal_id "
+ "WHERE dbuserprincipals.name = @loginUsername";
bool hasOtherRolesInDatabase;
using (var command = new SqlCommand(ROLES_IN_DATABASE_SQL, connection))
{
command.Parameters
.Add("@loginUsername", loginUsername);
using (var reader = command.ExecuteReader())
{
reader.Read();
hasOtherRolesInDatabase = reader.GetInt32(0) >= 1;
}
}
if (!hasOtherRolesInDatabase)
{
using (var command = new SqlCommand($"DROP USER [{loginUsername}]", connection))
{
command.ExecuteNonQuery();
}
}
}
return(new OperationResult(true));
}
public OperationResult AddRoleForUser(string serverAddress, string username,
string password, string catalog, string loginUsername, string roleName)
{
if (string.IsNullOrWhiteSpace(serverAddress))
{
throw new ArgumentNullException(nameof(serverAddress));
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException(nameof(username));
}
if (string.IsNullOrWhiteSpace(password))
{
throw new ArgumentNullException(nameof(password));
}
if (string.IsNullOrWhiteSpace(loginUsername))
{
throw new ArgumentNullException(nameof(loginUsername));
}
var hasInvalidSymbols = SqlStringValidator.HasInvalidSymbols(loginUsername);
if (hasInvalidSymbols)
{
return(new OperationResult(false, "The username contains illegal characters"));
}
var isInjectionAttempt = SqlStringValidator.HasInjectionCommand(loginUsername);
if (isInjectionAttempt)
{
return(new OperationResult(false, "The username contains illegal characters"));
}
var isValidRole = SqlStringValidator.IsValidRole(roleName);
if (!isValidRole)
{
return(new OperationResult(false, "The given role does not exist in SQL"));
}
var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password, catalog);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var hasCatalogUser = HasUserInDatabase(loginUsername, connection);
if (!hasCatalogUser)
{
// If the user doesn't have a mapping, we need to create a user
// in the target database prior to adding the role
using (var command = new SqlCommand($"CREATE USER [{loginUsername}] FOR LOGIN {loginUsername}", connection))
{
command.ExecuteNonQuery();
}
}
using (var command = new SqlCommand("sp_addrolemember", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters
.Add("@membername", loginUsername)
.Add("@rolename", roleName);
using (var _ = command.ExecuteReader())
{
// Nothing much to do here
}
}
}
return(new OperationResult(true));
}