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

Question

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?

Answer 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

• Timbalero: That is a good one :)