search

sqlstudent144 / SQL-Server-Scripts

My SQL Server Scripts
MIT License
71 stars 58 forks source link

Target database is in an availability group #20

Open mjhf19 opened 2 years ago

mjhf19 commented 2 years ago

I get the error below when running the following command against a SQL Instance which contains any databases configured for AlwaysOn

EXEC [dbo].[sp_DBPermissions] @DBName = 'ALL', @output = 'report';

"The target database ('db_name') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

This would require excluding databases in sys.dm_hadr_database_replica_states with is_primary_replica = 0

mjhf19 commented 2 years ago

I've successfully tested replacing the three cursor selects

currently: DECLARE cur_DBList CURSOR READ_ONLY FOR SELECT name FROM sys.databases WHERE state IN (0,5) AND source_database_id IS NULL ORDER BY name

new: DECLARE cur_DBList CURSOR READ_ONLY FOR SELECT d.name FROM sys.databases d left join sys.dm_hadr_database_replica_states h on d.replica_id = h.replica_id WHERE d.state IN (0,5) AND d.source_database_id IS NULL and (h.is_primary_replica is null or h.is_primary_replica = 1) ORDER BY d.name