Hi, I have an availability group with a primary and a secondary, I've added a read only Secondary for reporting purposes but the security isn't being inherited from the primary and I can't add the users to the database because it says it's read only. I'm probably missing something obvious but really can't see what it is.
Any advice gratefully received.
asked Feb 07 at 10:49 AM in Default
There are a number of things to consider when it comes to making sure that the logins are lined up on all the servers and databases when it comes to Availability Group security.
First of all, is it a Windows Login or a SQL Login that is being used, as these have some subtle differences when setting the logins up.
For a SQL Login, it is important to ensure that the SID and Password matches on all servers. If you execute the following on each of your replicas, you can then see if there is either a misaligned SID or the password hashes are different.
If there is a mismatch, then I would look to sync the login information from the Primary to the Secondary replicas. Either by scripting out the login details in T-SQL from the Primary and applying them to the Secondary. Alternatively, and probably the best way is by using the Copy-SQLLogin PowerShell cmdlet from the DBATools PowerShell module (https://dbatools.io/functions/).
If the Login is a Windows login, then simply creating the login on the server should work as the SID is normally pulled down from AD and should be the same over all of the servers.
answered Feb 07 at 11:39 AM