Limit connection to specific database with AlwaysOn Primary Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that they could access the database on the secondary but not the primary (ignore failover scenarios please, not the issue).
The problem now is that I need to grant that same user access to the SQLAgentReader role in msdb so they can see jobs on the primary.  That itself is not a problem, but I will need to enable the login on the primary in which case he could then get back into the first database on the primary.
Is there a trigger I could write to tell if he is 1) connecting to that first DB and 2) whether he is on the primary and if both are true then block his connection JUST to that DB?  Maybe there is some better solution (besides the obvious use two logins with different permissions)?  So far all I have found is triggers on login to the server, not at the database level.
There's not a straightforward, built-in way of accomplishing what you want to do beyond what you suggest at the end of your question:

besides the obvious use two logins with different permissions

I've heard of folks doing something like this:

create a SQL Server Agent job that runs once every X seconds (might be 1, 5, 15, etc depending on your requirements)
in that job, check to see if we're running on the primary replica for this database (sys.fn_hadr_is_primary_replica OR the AG DMVs)
if it is the primary, query sys.dm_exec_sessions to see if that login / user is connected ( in the login_name)
if they are connected, check to see if they are using the off-limits database (in the database_id column, or pull the query text and check that)
if they are using the off-limits database, use the kill command on the session_id

It's not perfect (because the job is not continuous, and because it's hard to check if the off-limits is being used conclusively), but it's better than nothing (if you're stuck with the single login approach).