3

This question already has an answer here:

I'm a sys admin being tossed in to a DBA role. I currently have two SQL Server boxes, 2016 Enterprise, in an AG. The DB has synchronized without any issues.

A third party vendor needs to have an identical user on both servers with the same User Mapping. But when I try to adjust the mappings on the secondary I get an error stating that the DB is in READ ONLY mode. How can I create this user with the correct mappings?

marked as duplicate by Tony Hinkle, John Eisbrener, kevinsky, LowlyDBA, mustaccio Oct 2 at 22:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4

You'll need to create the logins on each instance (box), but you can only set up the user mappings on the one that is currently acting as the "primary" for that AG (the one that is not read only).

If you fill out all the necessary information in the "New Login" dialog, including the "User Mapping" tab, then click the "Script" button at the top, you end up with something like this:

USE [master]
GO
CREATE LOGIN [ACME\jdarnell] FROM WINDOWS
GO
USE [VersionStoreTestDB]
GO
CREATE USER [ACME\jdarnell] FOR LOGIN [ACME\jdarnell]
GO
USE [VersionStoreTestDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ACME\jdarnell]
GO

For this test, I said to create a new login named "test-user" and map it to the "VersionStoreTestDB" database in the "db_datareader" role.

What you'll need to do is run the "CREATE LOGIN" portion on both boxes, and then run the "CREATE USER" and "ALTER ROLE" portions on just the primary box. The mappings will be replicated to the other box.

Note that this example uses a Windows-based login. If you're creating SQL logins, you will need to make sure and specify the SID on both servers. See Transferring Logins to a Database Mirror for more details on that, and a script for making the job easier.


At this point, you already know which server is the primary (since the one acting as the secondary gave you the error about the database being read only). You can check to if you're looking at the primary or the secondary in SSMS by expanding the "Always On High Availability" folder, then the "Availability Groups" folder. The availability groups listed will either be suffixed with "(Primary)" or "(Secondary)."

Not the answer you're looking for? Browse other questions tagged or ask your own question.