sp_help_revlogin2.ps1

sp_help_revlogin2.ps1

$source = "sqlserver"
$destination = "sqlcluster"

$smo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$smoext = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")

$sourceserver = New-Object Microsoft.SqlServer.Management.Smo.Server $source
$destserver = New-Object Microsoft.SqlServer.Management.Smo.Server $destination

foreach ($sourcelogin in $sourceserver.logins) {
	$destserver.refresh()
	$destserver.logins.refresh()
	$username = $sourcelogin.name
	 if ($destserver.logins[$username] -eq $null -and $sourcelogin.IsSystemObject -ne $true) {
	 
		$destlogin = New-Object Microsoft.SqlServer.Management.Smo.Login($destserver, $username)
		$destlogin.set_Sid($sourcelogin.get_Sid())
		if ($sourcelogin.PasswordPolicyEnforced) { $destlogin.PasswordPolicyEnforced = $true }
		if ($sourcelogin.PasswordExpirationEnabled) { $destlogin.PasswordExpirationEnabled = $true }

		$defaultdb = $sourcelogin.DefaultDatabase
		if ($destserver.databases[$defaultdb] -eq $null) { $defaultdb = "master" }
		$destlogin.DefaultDatabase = $defaultdb	
		
		if ($sourcelogin.LoginType -eq "SqlLogin") {
			$sql = "SELECT CAST(CONVERT(varchar(256), CAST(LOGINPROPERTY(name,'PasswordHash') 
				AS varbinary (256)), 1) AS nvarchar(max)) as hashedpass FROM sys.server_principals
				WHERE principal_id = $($sourcelogin.id)"

			$destlogin.LoginType = "SqlLogin"				
			$hashedpass = ($sourceserver.databases['master'].ExecuteWithResults($sql)).Tables.hashedpass		
			$destlogin.Create($hashedpass, [Microsoft.SqlServer.Management.Smo.LoginCreateOptions]::IsHashed)
		}	
		elseif ($sourcelogin.LoginType -eq "WindowsUser") {
			$destlogin.LoginType = "WindowsUser"
			$destlogin.Create()
		}
		else { Write-Warning "$($sourcelogin.LoginType) logins not supported. $($sourcelogin.name) skipped."; continue}
		
		
		$destlogin.refresh()
		if ($sourcelogin.DenyWindowsLogin) { $destlogin.DenyWindowsLogin = $true }
		if ($sourcelogin.IsDisabled) { $destlogin.Disable() }
		$destlogin.alter()
		
		# Server Roles: sysadmin, bulkadmin, etc
		foreach ($role in $sourceserver.roles) {
			if ($role.EnumMemberNames() -contains $sourcelogin.name) {
				if ($destserver.roles[$role.name] -ne $null) { $destlogin.AddToRole($role.name)}}
		}
		
		# Securables: Connect SQL, View any database, Administer Bulk Operations, etc.
		$perms = $sourceserver.EnumServerPermissions($username)
		foreach ($perm in $perms) {
			$permstate = $perm.permissionstate
			if ($permstate -eq "GrantWithGrant") { $grantwithgrant = $true; $permstate = "grant" } else { $grantwithgrant = $false }
			$permset = New-Object Microsoft.SqlServer.Management.Smo.ServerPermissionSet($perm.permissiontype)
			$destserver.$permstate($permset,$username,$grantwithgrant)
		}
		
		# Credential mapping
		$logincredentials = $sourceserver.credentials | Where-Object {$_.Identity -eq $sourcelogin.name}
		foreach ($credential in $logincredentials) {
			if ($destserver.Credentials[$credential.name] -eq $null) {
				$newcred = New-Object Microsoft.SqlServer.Management.Smo.Credential($destserver, $credential.name)
				$newcred.identity = $sourcelogin.name
				$newcred.Create()
			} else {
				$destlogin.AddCredential($credential.name)
			}
		}
	   # Database mappings are unnecessary because they're handled by the db migration.
	}
}