How can i use by making changes to below script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However this code would certainly not be at its best when i need to present for deployment across 300-400 SQL servers with some having always on others not.
Please help me in modifying /check if instance has AG enabled and execute with success
UPDATE As requested by @Kin
SET NOCOUNT ON DECLARE @user_to_be_added sysname SET @user_to_be_added = 'Domain\user' DECLARE @sqltext nvarchar(max) = N'' if (select serverproperty('IsHadrEnabled')) = 0 BEGIN -- change to your user to be added .. make sure that the login is already existing SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added) FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0 SELECT @sqltext END Else BEGIN SELECT @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added) FROM sys.dm_hadr_availability_replica_states ars inner join sys.databases dbs ON ars.replica_id = dbs.replica_id where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0 and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable. if (@sqltext <> '') begin select @sqltext end else begin print 'There is nothing to print - Probably its Secondary Replica of AG' end END