/*
    SQLInsight Pro: valgfri læseadgang til Dynamics AX' systemtabeller
    =====================================================================

    TILVALG. Agenten læser normalt kun metadata (grant-least-privilege.sql). Med dette script må den desuden læse de
    få AX-systemtabeller, som AX-tjekkene bruger, i ÉN navngiven AX-database, så SQLInsight kan advare om batchjob i
    fejl, forsinkede eller hængende batchjob, nummerserier og RecId'er der er ved at løbe tør:

        Axapta 3.0 og AX 4.0:  BATCH, NUMBERSEQUENCETABLE, NUMBERSEQUENCEREFERENCE, SYSTEMSEQUENCES, SQLDICTIONARY, DATAAREA
        AX 2009:               BATCHJOB, BATCHJOBHISTORY, NUMBERSEQUENCETABLE, NUMBERSEQUENCEREFERENCE, SYSTEMSEQUENCES,
                               SQLDICTIONARY, DATAAREA
        AX 2012 og F&O:        BATCHJOB, BATCHJOBHISTORY, NUMBERSEQUENCETABLE, NUMBERSEQUENCESCOPE, NUMBERSEQUENCEREFERENCE,
                               SYSTEMSEQUENCES, SQLDICTIONARY, DATAAREA

    Kun SELECT, kun disse tabeller: ingen kundedata (debitorer, ordrer, posteringer), ingen skriverettigheder. Scriptet
    finder selv AX-versionen og skemaet, tabellerne ligger i (bmssa i Axapta 3.0, dbo senere). Tabeller, der ikke findes
    i databasen, springes over. Scriptet kan køres igen uden skade. Fjern adgangen igen med revoke-dynamics-ax-read.sql.

    SQLInsight Web viser det samme som færdige GRANT-sætninger for hver AX-database (Dynamics AX → AX-databaser).
    AX 2009, 2012 og F&O er baseret på Microsofts dokumenterede skema – ikke verificeret mod kundedata.

    Understøtter SQL Server 2005 og nyere. Kør som sysadmin eller som db_owner/db_securityadmin i AX-databasen.
*/
SET NOCOUNT ON;

DECLARE @DatabaseName sysname;
DECLARE @LoginName sysname;
SET @DatabaseName = N'AXDB';                      -- <== ret til AX-databasen
SET @LoginName = N'DOMÆNE\svc-sqlinsight$';      -- <== ret til agentens login

IF DB_ID(@DatabaseName) IS NULL
BEGIN
    RAISERROR(N'Databasen %s findes ikke.', 16, 1, @DatabaseName);
    RETURN;
END;

IF SUSER_SID(@LoginName) IS NULL
BEGIN
    RAISERROR(N'Login %s findes ikke. Kør grant-least-privilege.sql først.', 16, 1, @LoginName);
    RETURN;
END;

DECLARE @Procedure nvarchar(300);
DECLARE @Sql nvarchar(max);
SET @Procedure = QUOTENAME(@DatabaseName) + N'.sys.sp_executesql';
SET @Sql = N'
SET NOCOUNT ON;
DECLARE @schema sysname;
DECLARE @precision int;
DECLARE @version nvarchar(10);
DECLARE @tables nvarchar(400);
DECLARE @user sysname;
DECLARE @grant nvarchar(max);
DECLARE @count int;

SELECT TOP 1 @schema = s.name, @precision = c.precision
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND UPPER(c.name) = N''RECID''
WHERE UPPER(t.name) = N''SQLDICTIONARY'' AND OBJECT_ID(QUOTENAME(s.name) + N''.DATAAREA'', N''U'') IS NOT NULL
ORDER BY CASE WHEN s.name = N''dbo'' THEN 0 ELSE 1 END, s.name;

IF @schema IS NULL
BEGIN
    DECLARE @database sysname;
    SET @database = DB_NAME();
    RAISERROR(N''%s er ikke en Dynamics AX-database (SQLDICTIONARY og DATAAREA findes ikke i samme skema). Intet er ændret.'', 16, 1, @database);
    RETURN;
END;

/* The same version rules as SQLInsight''s detection (Core/DynamicsAx/AxDetection.cs). */
IF @precision <= 10 SET @version = N''3.0'';
ELSE IF OBJECT_ID(QUOTENAME(@schema) + N''.BATCHJOB'', N''U'') IS NULL SET @version = N''4.0'';
ELSE IF OBJECT_ID(QUOTENAME(@schema) + N''.NUMBERSEQUENCESCOPE'', N''U'') IS NULL SET @version = N''2009'';
ELSE SET @version = N''2012'';

/* The tables per version: the same lists as the version adapters'' ReadTables (Core/DynamicsAx/AxSchemaAdapter.cs). */
SET @tables = CASE
    WHEN @version IN (N''3.0'', N''4.0'') THEN N''|BATCH|NUMBERSEQUENCETABLE|NUMBERSEQUENCEREFERENCE|SYSTEMSEQUENCES|SQLDICTIONARY|DATAAREA|''
    WHEN @version = N''2009'' THEN N''|BATCHJOB|BATCHJOBHISTORY|NUMBERSEQUENCETABLE|NUMBERSEQUENCEREFERENCE|SYSTEMSEQUENCES|SQLDICTIONARY|DATAAREA|''
    ELSE N''|BATCHJOB|BATCHJOBHISTORY|NUMBERSEQUENCETABLE|NUMBERSEQUENCESCOPE|NUMBERSEQUENCEREFERENCE|SYSTEMSEQUENCES|SQLDICTIONARY|DATAAREA|''
END;

SELECT @user = name FROM sys.database_principals WHERE sid = SUSER_SID(@login);
IF @user IS NULL
BEGIN
    SET @user = @login;
    SET @grant = N''CREATE USER '' + QUOTENAME(@user) + N'' FOR LOGIN '' + QUOTENAME(@login) + N'';'';
    EXEC (@grant);
    PRINT N''Bruger oprettet uden rettigheder: '' + @user;
END;

SET @grant = N'''';
SET @count = 0;
SELECT @grant = @grant + N''GRANT SELECT ON '' + QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name) + N'' TO '' + QUOTENAME(@user) + N''; '', @count = @count + 1
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = @schema AND CHARINDEX(N''|'' + UPPER(t.name) + N''|'', @tables) > 0;
IF @count > 0 EXEC (@grant);
PRINT N''Dynamics AX '' + @version + N'' i skemaet '' + @schema + N'': SELECT givet på '' + CAST(@count AS nvarchar(10)) + N'' tabeller i '' + DB_NAME() + N'' til '' + @user + N''.'';';
EXEC @Procedure @Sql, N'@login sysname', @login = @LoginName;