Is it possible to make particular users/group only go to SQL Server AlwaysOn Read only node outside Application Intent setting

  • Trying to work out if it is possible easily to direct particular users/groups to a SQL Server AlwaysOn read only node outside the Application intent setting.

    I want to allow/force people access to the read only databases via SSMS but concerned they will not remember to use the "Application Intent" Read-only setting all the time.

    I've seen some mention of AG routing but can't find anything that would do this on a user/group level.

    Many thanks


  • The routing configuration only details the routing preferences for replicas when in the secondary role.

    Users not only need to set the readonly application intent but also use the listener name and database name.

    Maybe a logon trigger that checks the current role (primary or secondary) and allows access based on this may be an option


    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I've been looking into this too lately. Could Resource Governor be used to point logins / groups to the read-only Servers? or can it be used to specify the "application intent"?

    Is it possible to set the 'execution context' of a stored procedure / adhoc query via a trigger?

  • Perry

    Thanks for the response, sounds like something I can use with the logon trigger.

    Appreciate your time to answer.


Viewing 4 posts - 1 through 4 (of 4 total)

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