We're installing a SQL Server Always On AG solution(SQL Server 2016) in a test-enviroment at our organisation. When testing the features we notice that all databases from a node are listed when we connect to a listener. Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?
Thanks in advance!
When testing the features we notice that all databases from a node are listed when we connect to a listener.
Correct, this is the expected behavior as the listener only "points" you to the instance where the primary databases for that availability group are currently located. It does not scope permissions or any other securable.
Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?
No, not in the way you're thinking due to what I states above that a listener is not a securable. If you only want an application to "see" a specific set of databases then set the permissions to those databases only. If you scope the permissions in this way, they will not show up under SSMS but the login will have access to them (assuming the login is not the database owner [this is not the same as the db_owner role]).
