Creating a SQL Login and User on an AlwaysOn Replica

Question

I have a database (DB1) which is synced to (DB2) on another server (via AlwaysOn - SQL Server 2014).

I want to give a user read access to DB2. I create a SQL login and database user in DB1. The new database user is migrated to DB2, but the SQL login is not (which makes sense).

The problem is that when I attempt to create a SQL login for the user on DB2 and give the user read rights to DB2, tied to the user account that came from DB1, I get an error saying DB2 is read only and cannot be modified.

So, how can I accomplish this?

Answer 1

You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is.

Then grant the permission on DB1. That permission will auto-magically get copied over to DB2 due to the AG.

There is no way to just grant permission on DB2. It has to be done on DB1. You can control access by disabling the login on DB1 though.

Answer 2

One option to automate sync'n the logins between your AG replicas, if desired, but can be used as a one time thing as well.

dbatools is a module that offers some code for migration of SQL Server instances, but I have used to also keep AG replicas in sync.

The main function you need to simply sync logins is Copy-SqlLogin. This will include the SID, password and other properties as seen via SSMS (database mappings, etc.). It is similar functionality that the script Tara referenced.

Copy-SqlLogin -Source DB1 -Destination DB2 -SyncOnly

That command alone will copy the logins from DB1 over to DB2 and simply sync up anything that changed. So say you add another database to the AG and grant a login access (map a user), then next time you run the above command it will update that login on DB2 for you.

The below script is included for completeness just to show what can be done. I built the below process on a client's 4 node AG as a Scheduled Task. As well this offers the ability to exclude replicas if you need; it was something they required.

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

Answer 3

You said you've just created the login on the first server, and the corresponding user is migrated to DB2. So all you need is to create the same login on the second server preserving it's sid, and you can do this first by copying the login's sid from sys.server_principals and then by creating the same login using with sid like this:

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

Answer 4

When you create the same login on the second server, you'll have to make sure to create it with the same SID and password as has been created on the Primary server. The steps that I follow is listed below:

• Deploy the 2 Stored Procedures (sp_hexadecimal and sp_help_revlogin) as provided in the link below on the Master DB of the Primary Replica – These SPs will help in generating login creation script which can then be run on Secondary Replica
  • https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
• Create a new login on Primary Replica

• Execute the following stored procedure, that was created as aprt of Step 1, passing in the “Login Name” to it

          EXEC sp_help_revlogin 'Login_Name'
  

• Copy the generated script from the above step and execute it on the Secondary replica

Answer 5

I believe that this is the sort of thing that partial containment is designed to address.

Please note that there are quite a few considerations if you choose to use a partially-contained database.

Here's how you can set that up using the primary (please remember to test in a non-production environment so that you can become aware of how your production environment may respond to the aforementioned list of considerations).

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