SQL - Logon trigger with blocks

USE master;
GO

CREATE TABLE LogonAttempts (
    LogonTime DATETIME DEFAULT GETDATE(),
    LoginName NVARCHAR(128),
    ClientHost NVARCHAR(128),
    ApplicationName NVARCHAR(128),
    IsSQLAuth BIT,
    IsAllowed BIT
);
GO



IF OBJECT_ID('AllowedGroupCache', 'U') IS NOT NULL
    DROP TABLE AllowedGroupCache;

-- Create an updated cache table with additional columns
CREATE TABLE AllowedGroupCache (
    GroupName NVARCHAR(128),
    MemberName NVARCHAR(128),
    MemberType NVARCHAR(60),          -- Type of account (Windows user or group)
    Privilege NVARCHAR(60),           -- Privilege level (e.g., user, admin)
    MappedLoginName NVARCHAR(128),    -- Mapped login name in SQL Server
    --PermissionPath NVARCHAR(255),     -- The path through which permission is granted
    LastUpdated DATETIME DEFAULT GETDATE()
);
go


CREATE or ALTER PROCEDURE RefreshAllowedGroupCache
AS
BEGIN
    -- Clear the current cache
    DELETE FROM AllowedGroupCache;
	--select * from AllowedGroupCache

    -- Define allowed Windows groups here
    DECLARE @AllowedGroups TABLE (GroupName NVARCHAR(128));
    INSERT INTO @AllowedGroups (GroupName)
	SELECT name    FROM sys.server_principals    WHERE type_desc = 'WINDOWS_GROUP'      AND is_disabled = 0;
    --VALUES ('DOMAIN\AllowedGroup1'), ('DOMAIN\AllowedGroup2'); -- Replace with actual allowed group names

    DECLARE @GroupName NVARCHAR(128);

    -- Loop through each allowed group and populate members
    DECLARE group_cursor CURSOR FOR SELECT GroupName FROM @AllowedGroups;
    OPEN group_cursor;
    FETCH NEXT FROM group_cursor INTO @GroupName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
       BEGIN TRY
            INSERT INTO AllowedGroupCache (MemberName, MemberType, Privilege, MappedLoginName, GroupName)
			--print concat('Group:',@GroupName)
            EXEC xp_logininfo @GroupName, 'members';
        END TRY
        BEGIN CATCH
            -- Handle any errors, e.g., if group members cannot be retrieved
            PRINT 'Could not retrieve members for group: ' + @GroupName;
        END CATCH;

        FETCH NEXT FROM group_cursor INTO @GroupName;
    END

    CLOSE group_cursor;
    DEALLOCATE group_cursor;
END;
GO




USE master;
GO

ALTER TRIGGER Restrict_Logon
ON ALL SERVER
WITH EXECUTE AS N'sa'
FOR LOGON
AS
BEGIN 

	SET NOCOUNT ON;
    DECLARE @LoginName NVARCHAR(128) = ORIGINAL_LOGIN();
    DECLARE @ClientHost NVARCHAR(128) = HOST_NAME();
    DECLARE @AppName NVARCHAR(128) = APP_NAME();
    --DECLARE @IsWindowsAuth BIT --= ORIGINAL_LOGIN() IN (SELECT name FROM sys.server_principals WHERE type_desc = 'WINDOWS_LOGIN');
	Declare @IsSQLLogin BIT 
    DECLARE @IsAllowed BIT = 0;

	--SELECT @IsSQLLogin = case when COALESCE((SELECT count(*) FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN' and name = ORIGINAL_LOGIN() ),0) = 0 THEN 0 ELSE 1 END
	SELECT @IsSQLLogin = CASE 
        WHEN COALESCE((SELECT COUNT(*) FROM sys.server_principals 
                       WHERE type_desc = 'SQL_LOGIN' AND name = @LoginName), 0) = 0 
			THEN 0
		WHEN EXISTS (SELECT 1 FROM master.dbo.AllowedGroupCache WHERE MemberName = @LoginName)
			THEN 0 
        ELSE 1 
    END;

	--SELECT name,* FROM sys.server_principals
	--select @@SERVERNAME
    -- Condition 1: Allow if the connection is from the SQL Server machine itself
    IF @ClientHost =@@SERVERNAME
    BEGIN
        SET @IsAllowed = 1;
    END
    -- Condition 2: Allow if the application name contains "Evolution"
    ELSE IF @AppName LIKE '%Evolution%' or @AppName = '.Net SqlClient Data Provider'
    BEGIN
        SET @IsAllowed = 1;
    END
    -- Condition 3: Allow if the user is using SQL Server authentication (not Windows authentication)
    ELSE IF @IsSQLLogin = 1
    BEGIN
        SET @IsAllowed = 1;
    END
	ELSE IF @LoginName like '%Pastel%'
	BEGIN
		SET @IsAllowed = 1;
	END

    -- Log the login attempt
    INSERT INTO master.dbo.LogonAttempts (LogonTime, LoginName, ClientHost, ApplicationName, IsSQLAuth, IsAllowed)
    VALUES (GETDATE(), @LoginName, @ClientHost, @AppName, @IsSQLLogin, @IsAllowed);

    -- If the login is not allowed, roll back
    IF @IsAllowed = 0
    BEGIN
        --ROLLBACK;
		INSERT INTO master.dbo.LogonAttempts (LogonTime, LoginName, ClientHost, ApplicationName, IsSQLAuth, IsAllowed)
		 VALUES (GETDATE(), @LoginName, @ClientHost, @AppName, @IsSQLLogin, @IsAllowed);
        --PRINT 'Logon denied: Logon conditions were not met.';
    END
END;
GO


--select * from LogonAttempts order by LogonTime DESC