• Mike Scalise - Wednesday, July 18, 2018 1:58 PM

    Hi,

    Is it dumb to make a CREATE LOGIN trigger on an AOAG primary such that when a login is created on the primary, it creates the same login on the secondary (with the same SID)?

    I think I'm experiencing the answer listed here:
    https://dba.stackexchange.com/questions/148224/sql-login-keeps-losing-access-to-databases

    When a mirrored database fails over, the SID in the database matches the SID stored in the mastermasterdatabase of the principal server (SERVER1SERVER1), not the mirrored server (SERVER2SERVER2). This natural, and default process of failover causes the SQL Login (MyReportAcctMyReportAcct) to become an orphaned account on the principal server (SERVER1SERVER1). This orphanhood process happens EVERY time there is a failover. The start of authority for SQL Logins are the instance that they were created (SERVER1SERVER1). The failover to the mirrored server (SERVER2SERVER2) is now a different start of authority, so the SIDs will be different, hence why the reports fail.

    Thanks,

    Mike

    I would guess you mean DDL trigger - a logon trigger fires when logging on. You'd need to do much more than just the trigger itself - if you were capturing the event data, you would still need to write something to create or alter or drop the login and set all of the properties depending. The issue they are talking about in that thread is the password for SQL logins. MS wrote a couple of stored procedures years ago so that will generate a script for the logins, including the password.
    How to transfer logins and passwords between instances of SQL Server

    What you may want to do is just generate a script that you can run on the secondary. And then you have all of the other things - server role membership, GDR permission, etc. Do a search on: sync logins availability groups. The first two or three pages have links to scripts to sync logins. They have differences in how they do them - running scripts on the secondary - some use linked servers, using SSIS, dbatools.io has a powershell cmdlet, which login types, etc. There is likely some script out there (or up here) that would work in a way you're comfortable with. Personally, I'd would use one of the scripts that generates a script on the primary to run on the replica.

    Sue