SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD = ' + sys.fn_varbintohexstr(password_hash) + ' HASHED, ' + 'SID = ' + sys.fn_varbintohexstr(sid) + ', DEFAULT_DATABASE = ' + default_database_name + ', DEFAULT_LANGUAGE = ' + default_language_name + ', CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 0 THEN 'OFF' ELSE 'ON' END + ', CHECK_POLICY = ' + CASE is_policy_checked WHEN 0 THEN 'OFF' ELSE 'ON' END + ';' AS create_statementFROM sys.sql_loginsWHERE name NOT LIKE '##%' AND name <> 'sa'UNION ALLSELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + default_database_name + ', DEFAULT_LANGUAGE = ' + default_language_name + ';'FROM sys.server_principalsWHERE type IN ( 'U', 'G' ) AND name NOT LIKE 'NT%\%'ORDER BY create_statement;
param ([string]$Principal, [string]$Mirror) [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $ErrorActionPreference = "Stop" $ps = New-Object Microsoft.SqlServer.Management.Smo.Server $Principal $ms = New-Object Microsoft.SqlServer.Management.Smo.Server $Mirror if (-not $ps.Databases.Count) { "Could not connect to $Principal" return } if (-not $ms.Databases.Count) { "Could not connect to $Mirror" return } "-------------------------------------------------------" "-- The following script should be run on $Mirror." "-------------------------------------------------------" foreach ($login in $ps.Logins) { if ("sa", "NT AUTHORITY\SYSTEM", "BUILTIN\ADMINISTRATORS" -contains $login.Name) { continue } if ($login.Name -match '##') { continue } $psid = "" if ("WindowsGroup", "WindowsUser" -notcontains $login.LoginType) { $login.Sid | % {$psid += ("{0:X}" -f $_).PadLeft(2, "0")} [byte[]] $phash = $ps.Databases["master"].ExecuteWithResults("select hash=cast(loginproperty('$($login.Name)', 'PasswordHash') as varbinary(256))").Tables[0].Rows[0].Hash $ppwd = "" $phash | % {$ppwd += ("{0:X}" -f $_).PadLeft(2, "0")} } $defaultDatabase = "master" $ms.Databases[$login.DefaultDatabase] | ? {$_.Status -eq "Normal"} | % {$defaultDatabase = $_.Name} $dropped = $false if ($ms.Logins[$login.Name]) { if ("WindowsGroup", "WindowsUser" -notcontains $login.LoginType) { $msid = "" $ms.Logins[$login.Name].Sid | % {$msid += ("{0:X}" -f $_).PadLeft(2, "0")} if ($psid -ne $msid) { "drop login [$($login.Name)];" $dropped = $true } [byte[]] $mhash = $ms.Databases["master"].ExecuteWithResults("select hash=cast(loginproperty('$($login.Name)', 'PasswordHash') as varbinary(256))").Tables[0].Rows[0].Hash $mpwd = "" $mhash | % {$mpwd += ("{0:X}" -f $_).PadLeft(2, "0")} if (-not $dropped -and $ppwd -ne $mpwd) { "alter login [$($login.Name)] with password = 0x$ppwd hashed;" } } if (-not $dropped -and $ms.Logins[$login.Name].DefaultDatabase -ne $defaultDatabase) { "alter login [$($login.Name)] with default_database = [$defaultDatabase];" } } if (-not $ms.Logins[$login.Name] -or $dropped) { if ("WindowsGroup", "WindowsUser" -contains $login.LoginType) { "create login [$($login.Name)] from windows with default_database = [$defaultDatabase];" } else { if ($login.PasswordExpirationEnabled) { $checkExpiration = "on" } else { $checkExpiration = "off" } if ($login.PasswordPolicyEnforced) { $checkPolicy = "on" } else { $checkPolicy = "off" } "create login [$($login.Name)] with password = 0x$ppwd hashed, sid = 0x$psid, default_database = [$defaultDatabase], check_policy = $checkPolicy, check_expiration = $checkExpiration;" if ($login.DenyWindowsLogin) { "deny connect sql to [$($login.Name)];" } if (-not $login.HasAccess) { "revoke connect sql to [$($login.Name)];" } if ($login.IsDisabled) { "alter login [$($login.Name)] disable;" } } } foreach ($role in $ps.Roles | ? {$_.Name -ne "public"}) { $addRole = $false if (-not $ms.Logins[$login.Name]) { $addRole = $login.IsMember($role.Name) } elseif ($dropped -or -not $ms.Logins[$login.Name].IsMember($role.Name)) { $addRole = $login.IsMember($role.Name) } if ($addRole) { "exec sp_addsrvrolemember @loginame = N'$($login.Name)', @rolename = N'$($role.Name)';" } } $permsql = @" select per.state_desc collate database_default + ' ' + per.permission_name collate database_default + ' TO [' + pri.name collate database_default + '];' perm_text from sys.server_permissions per join sys.server_principals pri on per.grantee_principal_id = pri.principal_id where class_desc = 'SERVER' and pri.name collate database_default = '$($login.Name)'"@ $psperms = $ps.Databases["master"].ExecuteWithResults($permsql).Tables[0].Rows $msperms = $ms.Databases["master"].ExecuteWithResults($permsql).Tables[0].Rows $mspermcol = @() foreach ($msp in $msperms) { $mspermcol += $msp.perm_text } foreach ($psp in $psperms) { $addPerm = $false if (-not $ms.Logins[$login.Name]) { $addRole = $true } elseif ($dropped -or $mspermcol -notcontains $psp.perm_text) { $addRole = $true } if ($addRole -and $psp.perm_text -notmatch "GRANT CONNECT SQL") { $psp.perm_text } } foreach ($db in $ms.Databases | ? {$_.Status -eq "Normal"}) { $user = $null $user = $db.Users | ? {$_.Login -eq $login.Name} if ($user) { if ($psid -ne "") { $usid = "" $user.Sid | % {$usid += ("{0:X}" -f $_).PadLeft(2, "0")} if ($usid -ne $psid) { "use $($db.Name); alter user [$($user.Name)] with login = [$($login.Name)];" } } } } } #if in principal databases, but not on mirror, add user $principalUsers = $ps.Databases | ? {$_.Status -eq "Normal"} | % {$db = $_.Name; $_.Users | ? {$_.Login -ne ""} | Select @{n="Database";e={$db}}, Name, Login} $mirrorUsers = $ms.Databases | ? {$_.Status -eq "Normal"} | % {$db = $_.Name; $_.Users | ? {$_.Login -ne ""} | Select @{n="Database";e={$db}}, Name, Login} foreach ($user in $principalUsers) { if (-not ($ms.Databases | ? {$_.Status -eq "Normal" -and $_.Name -eq $user.Database})) { continue } if ($ms.Databases[$user.Database].Users[$user.Name]) { continue } if (-not $ps.Logins[$user.Login]) { continue } "use [$($user.Database)]; create user [$($user.Name)] for login [$($user.Login)];" } #if in principal databases, but not on mirror, add to roles $principalDbRoles = $ps.Databases | ? {$_.Status -eq "Normal"} | % {$db = $_.Name; $roles = $_.Roles; $roles | % {$role = $_.Name; $_.EnumMembers() | ? {-not $roles[$_]} | Select @{n="Database";e={$db}}, @{n="Role";e={$role}}, @{n="Member";e={$_}}}} $mirrorDbRoles = $ms.Databases | ? {$_.Status -eq "Normal"} | % {$db = $_.Name; $roles = $_.Roles; $roles | % {$role = $_.Name; $_.EnumMembers() | ? {-not $roles[$_]} | Select @{n="Database";e={$db}}, @{n="Role";e={$role}}, @{n="Member";e={$_}}}} $lookup = @{} $mirrorDbRoles | % {$lookup.$($_.Database + ":" + $_.Role + ":" + $_.Member) = 1} foreach ($dbRole in $principalDbRoles) { if (-not $lookup.ContainsKey("$($dbRole.Database):$($dbRole.Role):$($dbRole.Member)")) { if (-not ($ms.Databases | ? {$_.Status -eq "Normal" -and $_.Name -eq $dbRole.Database})) { continue } "use [$($dbRole.Database)]; exec sp_addrolemember N'$($dbRole.Role)', N'$($dbRole.Member)';" } }
