Readd SQL Server Users

ReaddSqlUsers.ps1

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

$serverName = 'localhost';
$vbbUserName = '<<<Add username here>>>';
$databasesToWorkWith = @(<<<Added Databases Here>>>)

########################################
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $serverName;

$login = $server.Logins[$vbbUserName];
if($login -eq $null) {
    Write-Output "$login is not a valid SQL Server Login on this instance";
    break;
}

$loginName = $login.Name;

foreach($db in ($server.Databases | Where-Object Name -in $databasesToWorkWith )) {

    Write-Output ("{0}:" -f $db.Name);

    #Drop user from all databases if it is there
    $user = $db.Users | Where-Object Name -eq $loginName;
    if($user) { 
        Write-Output "`tRemoving currently existing user";
        $user.Drop(); 
    }

    #Add user with correspondent login to databases
    Write-Output "`tAdding user";
    $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $loginName);
    $usr.Login = $loginName;
    $usr.Create();

    #Add user permission
    Write-Output "`tAdding permissions";
    $dbname = $db.Name;
    $cn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverName;Integrated Security=True;Initial Catalog=$dbname");
    $cn.Open();

    $q = "EXEC sp_addrolemember @rolename = N'db_owner', @membername = N'$loginName'";
    $cmd = New-Object "System.Data.SqlClient.SqlCommand" ($q, $cn);
    $cmd.ExecuteNonQuery() | Out-Null;
    $cn.Close();

    Write-Output "";

}