I have a previous post about syncing users and logins via powershell, but seeing as that one uses the soon to be deprecated (if not already…no listing for this proc for sql server 2014 on MSDN…) ‘sp_change_users_login’, I thought I’d re-do it to be current. Use at your own risk.
Keep in mind, a database login can indeed be set to use a different server login, so just bear that in mind if you find this not syncing all of them. I don’t do that (and can’t imagine why anyone would want to make their lives more complicated by doing so….), so I’ve not coded for that scenario. This also looks for system logins with ‘##’ at the beginning and just skips those.
#requires -module SqlPS
Import-Module SqlPS -DisableNameChecking
$serverName = 'ServerName'
try{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
$logins = $srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | select -ExpandProperty Name
$srv.Databases | %{
$dbName = $_.Name
$sb = New-Object System.Text.StringBuilder
$_.Users | where{$_.Name -in $logins} | %{
if($_.Name -like '##*'){return};
$sb.AppendLine("ALTER USER $($_.Name) WITH LOGIN = $($_.Name);") | Out-Null
}
$_.ExecuteNonQuery($sb.ToString());
}
}
catch{
$_ | fl -Force
}
Sql Server Powershell 