主页 / dba / 问题 / 174946
Accepted
Randy Minder
Asked: 2017-05-31 11:41:59 +0800 CST

在 AlwaysOn 副本上创建 SQL 登录名和用户

  • 772

我有一个数据库（DB1），它同步到另一台服务器上的（DB2）（通过 AlwaysOn - SQL Server 2014）。

我想授予用户对 DB2 的读取权限。我在 DB1 中创建了一个 SQL 登录名和数据库用户。新的数据库用户迁移到 DB2，但 SQL 登录没有（这是有道理的）。

问题是，当我尝试在 DB2 上为用户创建 SQL 登录名并授予用户对 DB2 的读取权限时，与来自 DB1 的用户帐户相关联，我收到一条错误消息，指出 DB2 是只读的，无法修改。

那么，我该如何实现呢？

sql-server availability-groups

5 个回答

  1. Best Answer

    您需要在 DB2 服务器上创建登录。使用Robert 的脚本来获取 sid 和密码，如果它是 SQL 帐户的话。

    然后授予对 DB1 的权限。由于 AG，该权限将自动神奇地复制到 DB2。

    没有办法只授予对 DB2 的权限。它必须在 DB1 上完成。不过，您可以通过禁用 DB1 上的登录来控制访问。

    • 8

  2. 如果需要，一种自动同步您的 AG 副本之间的登录的选项，但也可以用作一次性的事情。

    dbatools是一个模块，它提供了一些用于迁移 SQL Server 实例的代码，但我过去常常也让 AG 副本保持同步。

    您需要简单同步登录的主要功能是Copy-SqlLogin. 这将包括通过 SSMS（数据库映射等）看到的 SID、密码和其他属性。Tara 引用的脚本具有类似的功能。

    Copy-SqlLogin -Source DB1 -Destination DB2 -SyncOnly

    仅该命令就会将登录信息从 DB1 复制到 DB2 并简单地同步任何更改的内容。因此，假设您将另一个数据库添加到 AG 并授予登录访问权限（映射用户），那么下次运行上述命令时，它将为您更新 DB2 上的登录。

    为了完整起见，包含以下脚本只是为了显示可以做什么。我在客户端的 4 节点 AG 上构建了以下流程作为计划任务。如果您需要，这还提供了排除副本的能力；这是他们需要的东西。

    #+++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
    • 2

  3. 您说您刚刚在第一台服务器上创建了登录，并将相应的用户迁移到 DB2。因此，您只需在第二台服务器上创建相同的登录名并保留它的 sid，您可以先从 sys.server_principals 复制登录名的 sid，然后使用with sid创建相同的登录名，如下所示：

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

  4. 当您在第二台服务器上创建相同的登录时，您必须确保使用与在主服务器上创建的相同的 SID 和密码来创建它。下面列出了我遵循的步骤：

    • 1

  5. 我相信这就是部分遏制旨在解决的问题。

    请注意，如果您选择使用部分包含的数据库，有很多注意事项。

    以下是您如何使用主节点进行设置（请记住在非生产环境中进行测试，以便您了解生产环境如何响应上述注意事项列表）。

    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
    • 0

相关问题