#MSSQL #Users Prevent login access to particular applications

emergency_rollback.bat

REM Required: "-A" for DAC connection. Optional "-E" for trusted connection (instead of "-U -P").
sqlcmd -S 127.0.0.1 -U MyAdmin -P Admin123 -q "exit(DROP TRIGGER [Restrict_Application_Access_Login_Trigger] ON ALL SERVER)" -A

prevent_application_access.sql

--Step1: Create Audit Table
USE master
GO
CREATE TABLE dbo.loginAuditTable (
    id INT IDENTITY PRIMARY KEY,
    data XML,
    program_name nvarchar(128)
)
GO

--Step2: Create VIEW To read XML Audit Data in user-friendly format
use master
go
CREATE VIEW dbo.loginAuditView
AS
SELECT id
      ,data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname') AS EventType
      ,data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime
      ,data.value('(/EVENT_INSTANCE/SPID)[1]', 'int') AS SPID
      ,data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(257)') AS ServerName
      ,data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS LoginName
      ,data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname') AS LoginType
      ,data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname') AS ClientHostName
      ,data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit') AS IsPooled
      ,program_name
      ,data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(85)') AS SID
FROM master.dbo.loginAuditTable
GO

--Step3: Create Logon Trigger to restrict application access
IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Restrict_Application_Access_Login_Trigger')
DROP TRIGGER [Restrict_Application_Access_Login_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER [Restrict_Application_Access_Login_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

  SET 
    CONCAT_NULL_YIELDS_NULL, 
    ANSI_WARNINGS, 
    ANSI_PADDING 
  ON

  DECLARE @data XML
  SET @data = ISNULL(EVENTDATA(),'')
 
  DECLARE @AppName nvarchar(300)
       ,@LoginName nvarchar(300)
       ,@LoginType nvarchar(300)
       ,@HostName nvarchar(300)

  IF @data.exist( '/EVENT_INSTANCE/SPID') = 1
  AND @data.exist( '/EVENT_INSTANCE/LoginName') = 1
  AND @data.exist( '/EVENT_INSTANCE/LoginType') = 1
  AND @data.exist( '/EVENT_INSTANCE/ClientHost') = 1
  BEGIN

    SELECT @AppName = ISNULL([program_name],'_')
    FROM master.sys.dm_exec_sessions
    WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(300)')
    IF @@ROWCOUNT = 0
    BEGIN
      SET @AppName = '_'
    END

    SELECT 
      @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(300)'),
      @LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(300)'),
      @HostName = @data.value('(/EVENT_INSTANCE/HostName)[1]', 'nvarchar(300)')

    IF @AppName LIKE 'Microsoft SQL Server Management Studio%'
       AND 
       @LoginName = 'test_user'
    BEGIN
      ROLLBACK; --Disconnect the session
        
      --Log the exception to our Auditing table
      INSERT INTO master.dbo.loginAuditTable(data, program_name) VALUES(@data, @AppName)
    END 
  END
END;