"Inner Join" works in query, not DB Connect

[timrich66]

Hi Knowledgeable People 😊

I have been provided a query which works successfully when run by the DBA on the server itself.  However, when run using DB Connect, 'no results' are found.  Please can someone advise why this may be?  Thanks

This is the query - 

SELECT ar.replica_server_name,
adc.database_name,
ag.name AS ag_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.is_commit_participant,
drs.synchronization_health_desc,
drs.recovery_lsn,
drs.truncation_lsn,
drs.last_sent_lsn,
drs.last_sent_time,
drs.last_received_lsn,
drs.last_received_time,
drs.last_hardened_lsn,
drs.last_hardened_time,
drs.last_redone_lsn,
drs.last_redone_time,
drs.log_send_queue_size,
drs.log_send_rate,
drs.redo_queue_size,
drs.redo_rate,
drs.filestream_send_rate,
drs.end_of_log_lsn,
drs.last_commit_lsn,
drs.last_commit_time
FROM master.sys.dm_hadr_database_replica_states AS drs
INNER JOIN master.sys.availability_databases_cluster AS adc
ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN master.sys.availability_groups AS ag
ON ag.group_id = drs.group_id
INNER JOIN master.sys.availability_replicas AS ar
ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.database_name;

[PickleRick]

The most obvious hint that comes to mind is that you might be running the query from a user that doesn't have sufficient privileges on the DB server for necessary objects.

[timrich66]

Thanks @PickleRick I have found that querying the master.sys.availability_groups used in the second INNER JOIN returns no results.  I have removed all the 'ag' references and the query runs ok.  

I've passed it back to the DBA to investigate.