Installer & configurer NRDOT pour MSSQL sur Windows auto-hébergé : authentification de domaine

SELECT SYSTEM_USER;

DECLARE @TargetUser SYSNAME = '<ACCOUNT_USERNAME>'; -- <-- Set your Windows user here

-- 1. Create the checklist of required permissions from your script
DECLARE @Checklist TABLE (Scope NVARCHAR(50), [Database] SYSNAME, [Permission] NVARCHAR(128));

INSERT INTO @Checklist VALUES
('Server', 'ALL', 'VIEW SERVER STATE'),
('Server', 'ALL', 'VIEW ANY DEFINITION'),
('Server', 'ALL', 'VIEW ANY DATABASE');

INSERT INTO @Checklist
SELECT 'Database Role', [name], 'db_datareader' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0
UNION ALL
SELECT 'Database Explicit', [name], 'VIEW DATABASE STATE' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0
UNION ALL
SELECT 'Database Explicit', [name], 'VIEW DEFINITION' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0;

-- 2. Gather existing explicit permissions
WITH ExplicitPerms AS (
    SELECT 'Server' AS Scope, CAST('ALL' AS NVARCHAR(128)) COLLATE DATABASE_DEFAULT AS [Database], p.permission_name COLLATE DATABASE_DEFAULT AS [Permission]
    FROM sys.server_permissions p JOIN sys.server_principals l ON p.grantee_principal_id = l.principal_id WHERE l.name = @TargetUser
    UNION ALL
    SELECT 'Database Role', d.name COLLATE DATABASE_DEFAULT, r.name COLLATE DATABASE_DEFAULT
    FROM sys.databases d CROSS APPLY (SELECT dp_role.name FROM sys.database_role_members drm JOIN sys.database_principals dp_role ON drm.role_principal_id = dp_role.principal_id JOIN sys.database_principals dp_user ON drm.member_principal_id = dp_user.principal_id JOIN sys.server_principals sp ON dp_user.sid = sp.sid WHERE sp.name = @TargetUser) r WHERE d.state = 0
    UNION ALL
    SELECT 'Database Explicit', d.name COLLATE DATABASE_DEFAULT, p.permission_name COLLATE DATABASE_DEFAULT
    FROM sys.databases d CROSS APPLY (SELECT db_p.permission_name FROM sys.database_permissions db_p JOIN sys.database_principals dp_user ON db_p.grantee_principal_id = dp_user.principal_id JOIN sys.server_principals sp ON dp_user.sid = sp.sid WHERE sp.name = @TargetUser) p WHERE d.state = 0
)
-- 3. Output the exact verification matrix
SELECT
    chk.Scope,
    chk.[Database],
    chk.[Permission] AS [Required Permission],
    CASE
        WHEN exp.[Permission] IS NOT NULL THEN 'GRANTED'
        WHEN IS_SRVROLEMEMBER('sysadmin', @TargetUser) = 1 THEN 'PASSED (via SysAdmin Role)'
        ELSE 'MISSING'
    END AS [Verification Status]
FROM @Checklist chk
LEFT JOIN ExplicitPerms exp ON chk.Scope = exp.Scope AND chk.[Database] = exp.[Database] AND chk.[Permission] = exp.[Permission]
ORDER BY chk.Scope, chk.[Database], chk.[Permission];

USE [master];
GO

-- 1. Grant Instance-level permissions to your Windows Account
GRANT VIEW SERVER STATE TO [<ACCOUNT_USERNAME>];
GRANT VIEW ANY DEFINITION TO [<ACCOUNT_USERNAME>];
GRANT VIEW ANY DATABASE TO [<ACCOUNT_USERNAME>];
GO

-- 2. Loop through all user databases and grant read/visibility permissions
DECLARE @name SYSNAME;
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution')
  AND [state] = 0; -- Only online databases

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT 'Granting permissions on database: ' + @name;

        -- Create the database user mapping if it doesn't exist, then grant roles
        SET @sql = '
            USE [' + @name + '];
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''<ACCOUNT_USERNAME>'')
            BEGIN
                CREATE USER [<ACCOUNT_USERNAME>] FOR LOGIN [<ACCOUNT_USERNAME>];
            END;
            GRANT VIEW DATABASE STATE TO [<ACCOUNT_USERNAME>];
            GRANT VIEW DEFINITION TO [<ACCOUNT_USERNAME>];
            ALTER ROLE db_datareader ADD MEMBER [<ACCOUNT_USERNAME>];';

        EXEC sp_executesql @sql;
        PRINT 'Success: ' + @name;
    END TRY
    BEGIN CATCH
        PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
    END CATCH

    FETCH NEXT FROM db_cursor INTO @name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

# Configure service to use domain account
# The space after obj= and password= is required syntax for sc.exe
sc.exe config "nrdot-collector" obj= "YOUR_DOMAIN\YOUR_USER_ACCOUNT" password= "<YOUR_PASSWORD>"

# Restart the service
Restart-Service nrdot-collector

Get-WmiObject Win32_Service -Filter "Name='nrdot-collector'" | Select Name, State, StartName
# Expected: StartName = YOUR_DOMAIN\YOUR_USER_ACCOUNT