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