function Remove-LegacyDatabaseUsers { <# .SYNOPSIS Remove the legacy database users (IIS App Pools) from the system .PARAMETER ConnectionString The connection string of the database to cleanup .PARAMETER DbName [Obsolete] The database name associated with this connection string #> [CmdletBinding()] [OutputType([void])] param ( $ConnectionString, $DbName ) Confirm-DatabaseAccess $ConnectionString $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString # The original passed in value is now obsolete, just use the one on the connection string now $DbName = $sqlConnection.Database if($DbName -match 'AlkamiMaster' -or $DbName -match 'DeveloperDynamic' ) { Write-Host "Cleaning crusty users from connection string: " $ConnectionString } else { # Only act on local Alkami databases return } $sqlConnection.Open() [System.Data.SqlClient.SqlCommand]$command = $sqlConnection.CreateCommand() $command.CommandText = "select [name] from [sys].[database_principals] where [type]='u' and [name]!='dbo';" [System.Data.SqlClient.SqlDataReader]$reader = $command.ExecuteReader() $DbNames = @() while ($reader.Read()) { $DbNames += $reader[0].ToString() } $reader.Dispose() # TODO: Should we death all users in AlkamiMaster and DeveloperDynamic no matter who they are? foreach ($account in (Get-SDKUserMatrix)) { # This will get rid of any IIS Users in the database # This does not get rid of the domain users in the database $username = $account.Username.Trim() if ($DbNames.Contains($username)) { [System.Data.SqlClient.SqlCommand]$command = $sqlConnection.CreateCommand() if($DbName -match 'AlkamiMaster' -or $DbName -match 'DeveloperDynamic' ) { $command.CommandText = "DROP USER [$username];" } else { $command.CommandText = "DROP LOGIN [$username];" } $command.ExecuteNonQuery() | Out-Null $command.Dispose() } } $sqlConnection.Dispose() }