0

I'm trying to map a login to a user in a database on the secondary replica of a two node High Availability group. Obviously this fails since the replica is offline. Is there a way for me to create the login mappings to users to the offline database? I'm using Windows domain accounts BTW, so no SID issues, just the mapping problem.

When connecting to the secondary, after a failover, the error is that "the users' default database could not be accessed." The default database is the one where on the primary I set the login to have the correct database mappings, and the correct default database. When the failover happens, the default database is correct, but the user is not mapped to the database. At all. I know these mappings are at the database level which is why this is throwing me for a loop.

  • Can you supply the code you're using and the error message? What do you mean the replica is "offline"? – George.Palacios May 15 at 13:37
  • The user itself should be replicated from the primary also - have you created the login on the secondary server using WITH SID and the SID from sys.sql_logins on the primary? – George.Palacios May 15 at 13:37
  • The secondary database in a High Availability group is always offline. I'm using Windows accounts so the SID issue isn't the problem. The login is created on the secondary. On a failover the user's default database comes over, but the database mappings do not. – Richard Wolford May 15 at 13:39
  • 1
    The existence of a user in a database with the same sid as a login is the mapping. There's no further mapping to do. – Tibor Karaszi May 15 at 13:46
  • 1
    @RichardWolford just as an idea, see if the row is present under sys.sql_logins - you may have accidentally created a SQL login rather than a Windows user. – George.Palacios May 15 at 14:04
1

If the login is a Windows login, and is present on both the Primary and Secondary servers, you should only need to add the database user to the Primary Replica.

This is because database users are at the database level and the creation of the user itself will be replicated to the secondary replica. As this is a Windows login, it then follows that the SID will already match.

  • That's exactly what I thought should be happening, but it doesn't seem to be doing so. Honestly I'm really confused as to what could be going wrong. Any idea on what I could check? – Richard Wolford May 15 at 13:41
  • @RichardWolford when you say it isn't happening, what do you mean? What error message are you getting? – George.Palacios May 15 at 13:41
  • When connecting to the secondary, after a failover, the error is that "the users' default database could not be accessed." The default database is the one where on the primary I set the login to have the correct database mappings, and the correct default database. When the failover happens, the default database is correct, but the user is not mapped to the database. At all. I know these mappings are at the database level which is why this is throwing me for a loop.\ – Richard Wolford May 15 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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