Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- $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.
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement