SQL Server – create user for a local Windows login from another instance (AlwaysOn)

All we need is an easy explanation of the problem, so here it is.

I have two SQL Server instances working as the AlwaysOn AG. There is a Windows login in each instance created from a local Windows account and mapped to respective users in the AG databases. Say I’m adding a new database to the AG.
Is there a way to pre-provision a user mapped to the local Windows login from another (secondary) node without having to failover the AG – so that when the actual failover occurs, that user is fully operational?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

To pre-provision the local Windows database accounts, add the local Windows database users to the database before you add the database to the AG. Steps below.

  1. create the new database on the secondary
  2. create the secondary node local login and database user
  3. backup the database and drop from the secondary
  4. restore the database to the primary
  5. create the primary node local login and database user
  6. add the database to the AG
  7. backup the database and transaction log
  8. restore the database and log backups to the secondary WITH NORECOVERY

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply