SQL Server 2012 – Managing Logins with Availability Groups

availability-groupsloginspermissionssql serversql-server-2012

I have created a user on my primary node that has access to all of the databases in Availability Group AG1. How do I ensure that same user has the same rights on all the other replicas in AG1?

According to this MSDN article (http://msdn.microsoft.com/en-us/library/hh270282(v=sql.110).aspx) I need to determine if they are contained databases first but I'm not entirely sure I have contained databases. The user that I created on the other replica's get's an error when I try to add him to the AG1 databases. The error indicates they are part of an Availability Group and are not accessible.

Best Answer

Assuming these are not contained databases, you need to worry about the logins (server level), not the users (database level). Contained users will be transferred automatically without difficulty.

Manually creating a login with the same name on the other server is not good enough - the SID will not match.

Robert Davis has written a post that describes how to generate the login scripts with the right SID and the right password hash. This is what you need to run on each of the replicas:

Transferring Logins to a Database Mirror

(Also see the comment thread on this answer.)

In order to just script all of the logins on the local server, change the stored procedure by commenting out this section (and the End that goes with it):

--If Not Exists (Select 1 From sys.server_principals
--          Where name = @LoginName)
--  Begin

(There are other, similar IF NOT EXISTS sections you'll want to comment out in order to get roles and permissions.)

Then call the stored procedure with hard-coded @@SERVERNAME and @debug = 1. This will print out CREATE LOGIN commands with the password hash and you can pick the ones you need and run those commands on the replicas.

Jonathan Kehayias wrote an SSMS add-in that handles all of the uncontained objects (don't forget about things like jobs), but I don't know if it has been maintained for newer versions of SSMS:

Synchronize Availability Group Logins and Jobs

Related Solutions

Sql-server – Adding a distributor for availability groups

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group.

Why?

Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the (remote/scaled out) Distributor.

Sql-server – Error 976 Severity: 14, State: 1. Always on Clustering

You are attempting to connect to databases on secondary replicas when the availabilty group replica is not configured to allow connections on anything other than the primary replica.

The following script will tell you the state of the secondary replicas, and whether they will accept read connections:

SELECT ag.name,
       replica_server_name,
       secondary_role_allow_connections_desc
FROM sys.availability_replicas ar
    JOIN sys.availability_groups ag
        ON ag.group_id = ar.group_id;

The result of each replica with be:

NO - no connections allowed to the database (default behavior)
READ_ONLY - connections allowed when the ReadIntent flag is passed in a connection string
ALL - all connections to the databases are permitted.

To change the setting for a replica you can either use the GUI, or a script:

ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON 'ReplicaName'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 'TCP://ReplicaName:Port'))

For more details see the Microsoft documentation.

Best Answer

Related Solutions

Sql-server – Adding a distributor for availability groups

Sql-server – Error 976 Severity: 14, State: 1. Always on Clustering

Related Question