The issue
The logins used in the databases included in an AG must share the same SID if you have SQL Server AlwaysOn, availability groups. How come? Let's investigate further...
A row is added to the [syslogins] table in the [master] system database each time a user is created.
Now, when you map a user to a database and begin adding roles to that database for the specific user principal, a row with the pertinent data is added to the [sysusers] user database table.
The [syslogins] and [sysusers] tables are connected based on SID information. Tada!.
You can query sys.database_principals to get a list of database principals. As shown below, we have similar SID’s for a login [SQLAg2User] and database user [SQLAg2user].
Environment information
The SQL Server Always On Availability Groups for these two nodes have the following characteristics for the purposes of this article:
- Primary Replica: SQLNode1\INST1
- Secondary Replica: SQLNode2\INST1
- Database: [MyNewDB]
The Solution
Enters T-SQL: You create the login on the primary replica node with the following script or something like that, that fits your needs:
-- Connect to the PRIMARY REPLICA node of the AG
USE [master]
GO
-- Create the login
CREATE LOGIN user1 WITH PASSWORD = N'aStrongPasswordHere';
If everything runs successfully you run the next to get the SID of the user:
USE [master]
GO
-- Get the SID of the user createdSELECT [sid], [name] FROM [syslogins] WHERE [name] = 'user1';
GO
Now, that you have the SID, switch the connection the secondary replica and execute the following query:
-- Connect to the **SECONDARY** REPLICA node of the AG
USE [master]
GO
CREATE LOGIN user1 WITH PASSWORD = N'useTheSamePassword', sid=<copy-paste the SID from above>;
Now, you are ready to go back to your Primary Replica that has the databases you want to grant the rights for the user and do so freely. The next time your database fails over to the other node it will work without a problem.
Just a side note: why Windows Authentication mode does not have this SID problem? Well, if you think about it, the SID is governed by one source: the Windows Active Directory.