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); } } } } } } } }
private static void DropDatabase(DatabaseSetupInfo setupInfo) { if (!string.IsNullOrEmpty(setupInfo.DatabaseName) && setupInfo.DatabaseExists()) { using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false))) { adminConnection.Open(); using (SqlCommand selectCommand = new SqlCommand("SELECT spid from sys.sysprocesses WHERE dbid in (SELECT database_id FROM sys.databases WHERE name=@Name)", adminConnection)) { selectCommand.Parameters.Add("@Name", System.Data.SqlDbType.VarChar).Value = setupInfo.DatabaseName; DataTable processIdsTable = new DataTable(); SqlDataAdapter processIdsAdapter = new SqlDataAdapter(selectCommand); processIdsAdapter.Fill(processIdsTable); using (SqlCommand killCommand = new SqlCommand("", adminConnection)) { foreach (DataRow dRow in processIdsTable.Rows) { killCommand.CommandText += string.Format("KILL {0};\r\n", dRow[0]); } killCommand.CommandText += string.Format("DROP DATABASE [{0}]", setupInfo.DatabaseName); killCommand.ExecuteNonQuery(); } } } } }
private static void RunCustomMethod(Action <SqlConnection> method, DatabaseSetupInfo databaseSetupInfo) { if (method != null) { using (SqlConnection conn = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true))) { conn.Open(); method(conn); } } }
public static void InsertLookups(KeyValuePair <string, string>[] lookupDataArray, DatabaseSetupInfo databaseSetupInfo) { foreach (var lookupData in lookupDataArray) { using (SqlConnection conn = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true))) { conn.Open(); InsertLookup(lookupData.Key, lookupData.Value, conn); } } }
private static bool CreateDatabase(DatabaseSetupInfo setupInfo, string createDatabaseObjectsSql, KeyValuePair <string, string>[] lookupDataArray) { if (!setupInfo.DatabaseExists()) { using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false))) { adminConnection.Open(); using (SqlCommand createCommand = new SqlCommand(string.Format("CREATE DATABASE [{0}]", setupInfo.DatabaseName), adminConnection)) { createCommand.ExecuteNonQuery(); } } return(true); } else { return(false); } }
public static void ExecuteDDL(string createDatabaseObjectsSql, DatabaseSetupInfo databaseSetupInfo) { string adminConnectionStringWithDb = databaseSetupInfo.CreateConnectionString(true, true); using (SqlConnection adminConnectionWDb = new SqlConnection(adminConnectionStringWithDb)) { adminConnectionWDb.Open(); using (SqlCommand createObjectsCommand = new SqlCommand("", adminConnectionWDb)) { createDatabaseObjectsSql += "\nGO"; // make sure last batch is executed. string sqlBatch = ""; foreach (string line in createDatabaseObjectsSql.Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) { if (line.ToUpperInvariant().Trim() == "GO") { if (!string.IsNullOrEmpty(sqlBatch)) { createObjectsCommand.CommandText = sqlBatch; try { createObjectsCommand.ExecuteNonQuery(); } catch { // TODO: Include parent window ID here MessageBox.Show(sqlBatch); throw; } } sqlBatch = string.Empty; } else { sqlBatch += line + "\n"; } } } } }
/// <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(); } } } } } } }