Create datadog user for each database in MSSQL

create-datadog-users-mssql.sql

-- https://docs.datadoghq.com/database_monitoring/setup_sql_server/gcsql/?tab=linuxhost&site=eu

DECLARE @databaseName NVARCHAR(255)
DECLARE @sql NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'USE [' + @databaseName + '];' + CHAR(10) + 
               'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''datadog'') ' + CHAR(10) +
               'BEGIN ' + CHAR(10) +
               '   CREATE USER datadog FOR LOGIN datadog;' + CHAR(10) +
               'END'
    
    PRINT @sql
    EXEC sp_executesql @sql

    FETCH NEXT FROM db_cursor INTO @databaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor