This post is a continuation of Multiple DB refresh automation using PowerShell post . The OP requested me to automate the login transfer process as well. The script should generate an outfile and execute the file on the target server.

<#

.ProblemStatement

Copying SQL server logins to file is little bit tricky though we are able to generate the script the password need to handled in a proper way to make it work.

Usually we’ll make use of scripts like sp_help_revlogin to manage copying logins from one instance to another but the requirement won’t suffice our purpose it won’t generate the script for server permissions and role assignments.

When you are automating the entire refresh process,PowerShell provides a flexibility to run SQL and generate the scripts in the said path. The SQL file can be invoked using invoke-sqlcmd to to execute it across any target server

The below code loads the snapins

SqlServerProviderSnapin100

SqlServerCmdletSnapin100

.Example1

To generate the script

PS:\>Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut

.Example2

Generate and apply script on to the target server

PS P:\> Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut -TargetServer <ServerName>

.Reference Link

#>

function Copy-Logins{
    [cmdletbinding()]
    Param
    (
    [parameter(Mandatory=$true)][string] $Sourceserver,
    [String] $TargetServer,
    [string] $outpath)
##Create a new outfile at query function call
[string]$FileDS = Get-Date -Format "yyyyMMdd" 
[string]$outFile = $outpath +'\'+ $Sourceserver+'_'+$FileTS + "_login.sql" 
New-Item -ItemType file $outfile -Force
##Prepare the connection string 
$SqlConnection = New-Object System.Data.SQLClient.SQLConnection("Server=$Sourceserver;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SQLClient.SqlCommand; 
$SqlCommand.Connection = $SqlConnection;   
Try
{
##SQL Account - Executing a SQL is better than working with Hashed value
$SQL=  "
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
   CASE 
WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
ELSE ' FROM WINDOWS WITH'
END 
   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa')"
$SQL2="SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa')"
$SQL3="
SELECT 
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
THEN SrvPerm.state_desc 
ELSE 'GRANT' 
END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
THEN '' 
ELSE ' WITH GRANT OPTION' 
END collate database_default AS [-- server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa')"
 
        $SqlCommand.CommandText = $SQL;   
        $SqlConnection.Open(); 
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL2;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL3;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
}
catch
{
 $ErrorMessage = $_.Exception.Message
 Write-host "$Sourceserver does't exist or Invalid SQL instance Name $ErrorMessage"
}
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin100'})) 
{
   add-pssnapin SqlServerProviderSnapin100 
   write-host "Loading SqlServerProviderSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerProviderSnapin100 is already loaded" 
} 
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'})) 
{   
    add-pssnapin SqlServerCmdletSnapin100 
   write-host "Loading SqlServerCmdletSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerCmdletSnapin100 is already loaded" 
} 
 If($TargetServer){
 try
    {
    Invoke-Sqlcmd -ServerInstance $TargetServer -InputFile $outFile -ErrorAction Stop
    }
catch
    {
     Write-host "$TargetServer not reachable "
     }
 }
}   
   

Please refer the below link which gives a full list of required details