public static void DropDatabaseUser(DatabaseSetupInfo setupInfo)
{
Action <SqlConnection> dropLoginMethod = (connection) =>
{
using (SqlCommand dropLoginCommand = new SqlCommand("sp_droplogin", connection))
{
dropLoginCommand.CommandType = CommandType.StoredProcedure;
dropLoginCommand.Parameters.Add("@loginame", SqlDbType.VarChar).Value = setupInfo.EffectiveApplicationAuthenticationInfo.UserName;
dropLoginCommand.ExecuteNonQuery();
}
};
using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
if (!setupInfo.ApplicationAuthenticationSameAsAdmin && !setupInfo.ApplicationAuthenticationInfo.IntegratedSecurity)
{
if (!setupInfo.IsServerRoleMember("sysadmin", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnection))
{
using (SqlCommand selectUsersCommand = new SqlCommand("exec sp_MSloginmappings @User", adminConnection))
{
selectUsersCommand.Parameters.Add("@User", SqlDbType.VarChar).Value = setupInfo.ApplicationAuthenticationInfo.UserName;
selectUsersCommand.CommandTimeout *= 4;
SqlDataAdapter adpt = new SqlDataAdapter(selectUsersCommand);
DataTable usersTable = new DataTable();
adpt.Fill(usersTable);
if (usersTable.Rows.Count == 0)
{
dropLoginMethod(adminConnection);
}
else if (usersTable.Rows.Count == 1 && usersTable.Rows[0]["DBName"].ToString().Equals(setupInfo.DatabaseName, StringComparison.InvariantCultureIgnoreCase))
{
using (var adminConnectionWithDB = new SqlConnection(setupInfo.CreateConnectionString(true, true)))
{
adminConnectionWithDB.Open();
if (!setupInfo.IsDatabaseRoleMember("db_owner", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnectionWithDB))
{
using (var dropUserCommand = new SqlCommand(string.Format("DROP USER [{0}]", setupInfo.ApplicationAuthenticationInfo.UserName), adminConnectionWithDB))
{
dropUserCommand.ExecuteNonQuery();
}
dropLoginMethod(adminConnection);
}
}
}
}
}
}
}
}
/// <summary>
/// Creates a new user in the database if needed
/// </summary>
public static void CreateDatabaseUser(DatabaseSetupInfo databaseSetupInfo, string[] neededTables)
{
if (!databaseSetupInfo.ApplicationAuthenticationSameAsAdmin)
{
string userName;
Func <SqlConnection, SqlCommand> createLoginMethod;
if (databaseSetupInfo.EffectiveApplicationAuthenticationInfo.IntegratedSecurity)
{
userName = @"NT AUTHORITY\NETWORK SERVICE";
createLoginMethod = (connection) => new SqlCommand(string.Format("CREATE LOGIN [{0}] FROM WINDOWS", userName), connection);
}
else
{
userName = databaseSetupInfo.EffectiveApplicationAuthenticationInfo.UserName;
createLoginMethod = (connection) => new SqlCommand(
string.Format(
"CREATE LOGIN [{0}] WITH PASSWORD='******', DEFAULT_DATABASE=[{2}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF",
userName,
databaseSetupInfo.EffectiveApplicationAuthenticationInfo.Password,
databaseSetupInfo.DatabaseName
),
connection
);
}
using (SqlConnection adminConnection = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
using (SqlConnection adminConnectionWithDb = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true)))
{
adminConnectionWithDb.Open();
SqlCommand selectUserCommand = new SqlCommand("SELECT COUNT(*) FROM sys.database_principals AS db INNER JOIN sys.server_principals AS S ON db.sid = s.sid WHERE db.name=@UserName OR S.name=@UserName", adminConnectionWithDb);
selectUserCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName;
bool userExists = (int)selectUserCommand.ExecuteScalar() > 0;
if (!userExists)
{
using (SqlCommand loginExistsCommand = new SqlCommand("SELECT COUNT(*) FROM sys.server_principals WHERE NAME=@UserName", adminConnection))
{
loginExistsCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName;
var loginExists = (int)loginExistsCommand.ExecuteScalar() > 0;
if (!loginExists)
{
SqlCommand createLoginCommand = createLoginMethod(adminConnection);
createLoginCommand.ExecuteNonQuery();
}
SqlCommand createUserCommand = new SqlCommand(string.Format("CREATE USER [{0}] FOR LOGIN [{0}]", userName), adminConnectionWithDb);
createUserCommand.ExecuteNonQuery();
}
}
using (SqlCommand permissionsCommand = new SqlCommand("", adminConnectionWithDb))
{
if (neededTables == null || neededTables.Length == 0)
{
if (!databaseSetupInfo.IsDatabaseRoleMember("db_owner", userName, adminConnectionWithDb))
{
permissionsCommand.CommandText = "sp_addrolemember";
permissionsCommand.CommandType = System.Data.CommandType.StoredProcedure;
permissionsCommand.Parameters.Add("@rolename", System.Data.SqlDbType.VarChar).Value = "db_owner";
permissionsCommand.Parameters.Add("@membername", System.Data.SqlDbType.VarChar).Value = userName;
permissionsCommand.ExecuteNonQuery();
}
}
else
{
permissionsCommand.CommandType = System.Data.CommandType.Text;
foreach (string tableName in neededTables)
{
permissionsCommand.CommandText = string.Format("GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE,VIEW DEFINITION ON [dbo].[{0}] TO [{1}]", tableName, userName);
permissionsCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
