I have an Always On Availability Group with a read-only secondary set up. We're running SQL Server 2016 Enterprise Edition SP1 CU4. Users are supposed to only connect to the secondary using the ReadOnly ApplicationIntent when connecting to the listener. I don't want to remove their logins from the primary since this would prohibit access after a failover. I am looking at perhaps running a SQL Agent job that checks connections and kills sessions if they are running on the primary but it seems like there should be a better way to do that than running a job every minute or so! Do I need to look at creating an audit or is there some other mechanism to accomplish this?
Logon trigger is what coming to my mind, there may be other better way – https://dba.stackexchange.com/users/42379
I'd agree with the easiest way being a logon trigger. It can check the status of one of the databases in your AG, and if it is the primary prevent logon for that user. – https://dba.stackexchange.com/users/11639
Note that if a logon trigger starts failing for some reason, then nobody gets logged in except the dedicated admin connection. So catch any errors and handle with care. - https://dba.stackexchange.com/users/63284