How to restrict the results from sys.databases to available databases?
We are changing to availability groups and replica server running on sql-server 2017.
Before this I had a select statement running on master-database to see which datebases are running on the server (select name, state from sys.databases) and then let the user select a database and setup a connection to the database for further sql-statements.
But now on the new system with availability groups and replica-server, I see more databases, which I cannot connect to, when I open a connection to single availability group.
The select statements shows all databases even if they belong to another availability group (they are hosted by the same replica-server).
The problem is that for an dbo its no problem to figured out which databases belongs to the availability group (sys.availabiltiy - Views), but the normal user doesn't have the needed rights to view these sys-Views and I don't want to give him view_server_stats role.