x

Adding a user to a DB on a Read only AG

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.

more ▼

asked Feb 07 at 10:49 AM in Default

avatar image

Mrs_Fatherjack
5.2k 65 68 77

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

 DECLARE @LoginOfInterest SYSNAME = N'LoginName'
 
 SELECT  sp.name AS ServerLoginName ,
         sp.type_desc AS ServerLoginDesc ,
         CONVERT(VARCHAR(MAX), LOGINPROPERTY(sp.name, 'passwordhash'), 1) AS ServerLoginPasswordHash ,
         dp.name AS DatabaseUserName ,
         dp.type_desc AS DatabaseUserDesc
 FROM    sys.server_principals AS sp
         LEFT JOIN sys.database_principals AS dp ON sp.sid = dp.sid
 WHERE   sp.name = @LoginOfInterest;
 GO

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.

more ▼

answered Feb 07 at 11:39 AM

avatar image

jqmartin
40 1

Thank you so much, the code worked.

Feb 07 at 12:15 PM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x210
x2
x2

asked: Feb 07 at 10:49 AM

Seen: 66 times

Last Updated: Feb 07 at 12:15 PM

Copyright 2017 Redgate Software. Privacy Policy