Criando um login e usuário SQL em uma réplica AlwaysOn

Você precisa criar o login no servidor DB2. Use o script de Robert para que ele pegue o sid e a senha, bem se for uma conta SQL que é.

Em seguida, conceda a permissão no DB1. Essa permissão será automaticamente copiada para o DB2 devido ao AG.

Não há como apenas conceder permissão no DB2. Tem que ser feito no DB1. No entanto, você pode controlar o acesso desabilitando o login no DB1.

Uma opção para automatizar a sincronização dos logins entre suas réplicas de AG, se desejado, mas também pode ser usada uma única vez.

dbatools é um módulo que oferece algum código para migração de instâncias do SQL Server, mas eu também costumava manter as réplicas do AG sincronizadas.

A principal função que você precisa para simplesmente sincronizar logins é Copy-SqlLogin. Isso incluirá o SID, a senha e outras propriedades vistas via SSMS (mapeamentos de banco de dados, etc.). É uma funcionalidade semelhante à que o script Tara referenciou .

Copy-SqlLogin -Source DB1 -Destination DB2 -SyncOnly

Esse comando sozinho copiará os logins do DB1 para o DB2 e simplesmente sincronizará qualquer coisa que tenha sido alterada. Digamos que você adicione outro banco de dados ao AG e conceda um acesso de login (mapear um usuário), então da próxima vez que você executar o comando acima, ele atualizará esse login no DB2 para você.

O script abaixo está incluído para completar apenas para mostrar o que pode ser feito. Eu construí o processo abaixo no AG de 4 nós de um cliente como uma tarefa agendada. Além disso, oferece a capacidade de excluir réplicas, se necessário; era algo que eles precisavam.

#+++For debug just remove the "ErrorAction" and "WarningAction" parameters from the command+++
$localServerName = 'DB1'
$AGName = 'AG1'
$replicaExclude = 'DB3'
$excludeJobList = 'syspolicy_purge_history','DatabaseBackup - SYSTEM_DATABASES - FULL','DatabaseBackup - USER_DATABASES - DIFF','DatabaseBackup - USER_DATABASES - FULL','DatabaseBackup - USER_DATABASES - LOG'
$logfile = 'C:\Scripts\SyncProcess.log' 

# Where I had stored the module on the client computer
Import-Module 'C:\Scripts\dbatools\dbatools.psm1'

if (Test-Path $logfile) {Remove-Item $logfile -Force}
Start-Transcript -Path $logfile -Force -NoClobber

$r = Get-SqlAvailabilityGroup -SqlServer $localServerName -AvailabilityGroupname $AGName -IsPrimary
if ($r)
{
    $srvs = (Get-SqlAvailabilityGroup -SqlServer $localServerName -AvailabilityGroupname $AGName | where {$_.ReplicaName -ne $localServerName -and $replicaExclude -NotContains $_.ReplicaName}).ReplicaName
    foreach ($s in $srvs)
    {
        Write-Host "Working on $s" -ForegroundColor Red
        #Copy logins to destination, ignoring errors and warning messages to keep output low
        Copy-SqlLogin -Source $localServerName -Destination $s -SyncOnly -ErrorAction SilentlyContinue -WarningAction SilentlyContinue

        #copy operators
        Copy-SqlOperator -Source $localServerName -Destination $s

        #Copy jobs to destination
        Copy-SqlJob -Source $localServerName -Destination $s -Force -Exclude $excludeJobList
    }
}
Stop-Transcript

Você disse que acabou de criar o login no primeiro servidor e o usuário correspondente foi migrado para o DB2. Então tudo que você precisa é criar o mesmo login no segundo servidor preservando seu sid, e você pode fazer isso primeiro copiando o sid do login de sys.server_principals e depois criando o mesmo login usando com sid assim:

create login my_login with password = '*****', sid = 0x6E94CADC0A6EEC439B620643CDDB5C71 -- put here your login's sid 

Ao criar o mesmo login no segundo servidor, você terá que criá-lo com o mesmo SID e senha que foram criados no servidor Primário. Os passos que sigo estão listados abaixo:

• Implante os 2 procedimentos armazenados ( sp_hexadecimal e sp_help_revlogin ) conforme fornecido no link abaixo no banco de dados mestre da réplica primária – esses SPs ajudarão na geração do script de criação de logon que pode ser executado na réplica secundária
  • https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
• Criar um novo logon na réplica primária

• Execute o seguinte procedimento armazenado, que foi criado como aprt da Etapa 1, passando o “Nome de Login” para ele

          EXEC sp_help_revlogin 'Login_Name'
  

• Copie o script gerado da etapa acima e execute-o na réplica secundária

Acredito que esse é o tipo de coisa que a contenção parcial foi projetada para resolver.

Observe que há algumas considerações se você optar por usar um banco de dados parcialmente contido.

Veja como você pode configurar isso usando o primário (lembre-se de testar em um ambiente de não produção para que você possa saber como seu ambiente de produção pode responder à lista de considerações mencionada acima).

use master;
go
sys.sp_configure 'contained database authentication',1;
go
reconfigure with override;
go

use master;
go
alter database DB1 set containment = partial;
go


use DB1
go
create user test with password=N'#c0n+@Ined!';
go
use DB1
go
alter role db_datareader add member test;
go