Create HealthService Login as Low Priv in SQL 2016

Microsoft.SQLServer.RunAs.2016.Addendum.HealthServiceLowPriv.Task (Task)

Element properties:

Member Modules:

Source Code:

<Task ID="Microsoft.SQLServer.RunAs.2016.Addendum.HealthServiceLowPriv.Task" Accessibility="Internal" Enabled="true" Target="SQL2016Disc!Microsoft.SQLServer.2016.DBEngine" Timeout="300" Remotable="true">

  <Category>Maintenance</Category>

  <ProbeAction ID="PA" TypeID="System!System.CommandExecuterProbe">

    <ApplicationName/>

    <WorkingDirectory>$Target/Property[Type="SQL2016Disc!Microsoft.SQLServer.2016.DBEngine"]/ToolsPath$\Binn</WorkingDirectory>

    <CommandLine>sqlcmd.exe -E -S $Target/Property[Type="SQL2016Disc!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$ -Q "SET NOCOUNT ON;DECLARE @accountname nvarchar(128);DECLARE @command1 nvarchar(MAX);DECLARE @command2 nvarchar(MAX);DECLARE @command3 nvarchar(MAX);SET @accountname = 'NT SERVICE\HealthService';SET @command1 = 'USE [master];CREATE LOGIN ['+@accountname+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master];';SET @command2 = '';SELECT @command2 = @command2 + 'USE ['+db.name+'];CREATE USER ['+@accountname+'] FOR LOGIN ['+@accountname+'];' FROM sys.databases db left join sys.dm_hadr_availability_replica_states hadrstate on db.replica_id = hadrstate.replica_id WHERE db.database_id &lt;&gt; 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role is null);SET @command3 = 'USE [master];GRANT VIEW ANY DATABASE TO ['+@accountname+'];GRANT VIEW ANY DEFINITION TO ['+@accountname+'];GRANT VIEW SERVER STATE TO ['+@accountname+'];GRANT SELECT on sys.database_mirroring_witnesses to ['+@accountname+'];USE [msdb];EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';EXECUTE sp_executesql @command1;EXECUTE sp_executesql @command2;EXECUTE sp_executesql @command3;"</CommandLine>

    <TimeoutSeconds>120</TimeoutSeconds>

    <RequireOutput>true</RequireOutput>

    <Files/>

  </ProbeAction>

</Task>