Post reply

CREATE LOGIN Trigger

  • July 18, 2018 at 1:58 pm

    #354697

    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

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • July 19, 2018 at 7:24 pm

    #1998322

    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

  • July 20, 2018 at 7:14 am

    #1998379

    Sue_H - Thursday, July 19, 2018 7:24 PM

    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

    Sue,

    Thank you for the response.

    Yes, I was referring to a DDL trigger (when a login gets created), not a CREATE LOGIN trigger. Thanks for setting me straight on that.

    And yes, I have a couple of the official Microsoft SPs for transferring logins with their passwords etc., so I was planning on generating a script that would be executed on the replica. I will do the search you suggested--no sense in re-inventing the wheel if other users have come up with solutions that work well (e.g., using linked servers or dbatools).

    Again, thanks for taking the time to reply.

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply