Special Purpose Script: Monitoring App ID Login Source

In this particular situation, we're looking at an application ID that has db_owner role membership on the application's database. As developers are wont to do, they are logging in using the application ID to do development and administrative work, since it has owner rights. This is, for obvious reasons, a Bad Thing ™. Knowing that the application should only be logging in from the web server, we have a SQL Agent job call the following procedure every 5 minutes, to determine where the ID is coming from in each process, and raise a ruckus if the ID is coming from somewhere it shouldn't, like a developer workstation. The DBA support database in this case, is called "ApplicationSupport".

Of particular interest in this script, I'd like to point out the section where we insert the list of acceptable servers into a table, to compare against the list of hosts present in sysprocesses. The caller can provide a single hostname, or an unlimited comma delimited list of hostnames. The script parses the comma delimited list, if present, and inserts each hostname into a single column table, comparable to sysprocesses.

First, the required tables.

  
/****** Object:  Table [dbo].[UsrLoginHist] Script Date: 01/11/2010 07:44:43 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[UsrLoginHist] ( 
  [LogID] [int] IDENTITY(1,1) NOT NULL, 
  [LogDT] [datetime] NOT NULL 
    CONSTRAINT [DF_UsrLoginHist_LogDT] DEFAULT (getdate()), 
  [LoginName] [varchar](100) NULL, 
  [Host] [varchar](30) NULL, 
  [DBName] [varchar](100) NULL, 
  [SPID] [int] NULL, 
  [LoginDT] [datetime] NULL, 
  [BatchDT] [datetime] NULL, 
    CONSTRAINT [PK_UsrLoginHist_LogID] 
    PRIMARY KEY CLUSTERED ([LogID] ASC) 
  ) 
GO 
  
CREATE NONCLUSTERED INDEX [IXNN_UsrLoginHist_Host] 
  ON [dbo].[UsrLoginHist] ([Host] ASC) 
    INCLUDE ([DBName], [LoginName]
  ) 
GO 
  
CREATE NONCLUSTERED INDEX [IXNN_UsrLoginHist_LogDT] 
  ON [dbo].[UsrLoginHist] ([LogDT] ASC) 
GO 
  
CREATE NONCLUSTERED INDEX [IXNN_UsrLoginHist_LoginDT] 
  ON [dbo].[UsrLoginHist] ([LoginDT] ASC) 
  
/****** Object:  Table [dbo].[UsrLoginLastScan] Script Date: 01/11/2010 07:45:02 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[UsrLoginLastScan] ( 
  [LoginName] [varchar](100) NULL, 
  [Host] [varchar](30) NULL, 
  [DBName] [varchar](100) NULL, 
  [SPID] [int] NULL, 
  [LoginDT] [datetime] NULL, 
  [BatchDT] [datetime] NULL 
) 

Here's the stored procedure.

/****** Object:  StoredProcedure [dbo].[ScanUserLogin] Script Date: 12/23/2009 10:52:07 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [dbo].[ScanUserLogin] 
/******************************************************************************* 
NAME: 
  ScanUserLogin 
DESCRIPTION: 
  Audits sessions for a particular user ID on this server, alerts on any new ones 
  that are not coming from authorized hosts. 
DEPENDENCIES: 
  ApplicationSupport.dbo.UsrLoginHist 
  ApplicationSupport.dbo.UsrLoginLastScan 
TEST EXEC: 
  EXEC dbo.ScanUserLogin 
    @LoginName = 'MAXIMO', 
    @AuthHostList = 'HDCDEV04,HDCDEV06' 
TO DO LIST: 
  Pending feedback. 
NOTES:  
  Please send any comments or suggestions to dmmaxwell@gmail.com. 
******************************************************************************** 
AUTHOR - EXT. - DATE - REASON FOR CHANGE 
------------------------------------------------------------------------------- 
David Maxwell - 61142 - 2009.12.22 - Initial version. 
*******************************************************************************/ 
  @LoginName varchar(30), -- The login name to search for.  Required. 
  @HostList varchar(350), -- The comma-delimited list of acceptable hosts. Required. 
  @RetainDays int -- The number of days worth of history to keep for this ID. 
AS 
  
SET NOCOUNT ON -- Because, why? 
  
-- Required variables 
DECLARE 
  @ErrMsg nvarchar(4000), -- Error message holder. 
  @HostName varchar(30), -- Hostname being added to the authorized host list. 
  @LoginCount int, -- Number of new, unauthorized logins being reported. 
  @LogDate datetime, -- The recorded logging date when the scan was run. 
  @PurgeDate datetime -- The date from which to purge old records from history. 
  
-- Set constants 
SELECT @LogDate = GETDATE() 
SELECT @PurgeDate = DATEADD(day,-@RetainDays,@LogDate) 
  
-- Clean up from previous runs, if necessary 
IF (SELECT OBJECT_ID('tempdb.dbo.#UsrLoginScan')) IS NOT NULL 
DROP TABLE #UsrLoginScan; 
  
IF (SELECT OBJECT_ID('tempdb.dbo.#UsrLoginScan')) IS NOT NULL 
DROP TABLE #HostList; 
  
-- Create required temp tables. 
BEGIN TRY 
  CREATE TABLE #UsrLoginScan ( 
    LoginName varchar(100) NULL, 
    Host varchar(30) NULL, 
    DBName varchar(100) NULL, 
    SPID int NULL, 
    LoginDT datetime NULL, 
    BatchDT datetime NULL, 
    Notify bit NOT NULL 
  ); 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to create #UsrLoginScan: ' + char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
BEGIN TRY 
  CREATE TABLE #HostList ( 
    Host varchar(30) NOT NULL 
  ) 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to create #HostList: ' + char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
-- Insert the list of names into the host list. 
WHILE (SELECT LEN(@HostList)) > 0 
BEGIN 
  BEGIN TRY 
    -- If the list still contains commas, we're not done, so... 
    IF (SELECT CHARINDEX(',',@HostList,1)) > 0 
    BEGIN 
      -- Get the name at the beginning of the list. 
      SELECT @HostName = LEFT(@HostList,CHARINDEX(',',@HostList,1)) 
    END 
    ELSE 
      -- Otherwise, just get the remaining name in the list. 
      SELECT @HostName = @HostList 
    
    -- Insert the host name into the list, sans comma (if there). 
    INSERT INTO #HostList 
    SELECT REPLACE(@HostName,',','') 
  
    -- Remove the host name from the list, with comma (if there). 
    SELECT @HostList = REPLACE(@HostList,@HostName,'') 
  END TRY 
  BEGIN CATCH 
    SELECT @ErrMsg = N'Unable to add host names to #HostList: ' +  char(13) + 
    ERROR_MESSAGE() 
    GOTO ExitProc -- FATAL ERROR 
  END CATCH 
END 
  
-- Get the current list of unauthorized logins from sysprocesses. 
BEGIN TRY 
  INSERT INTO #UsrLoginScan 
  SELECT 
    sp.loginame, 
    sp.hostname, 
    db_name(sp.[dbid]), 
    sp.SPID, 
    sp.login_time, 
    sp.last_batch, 
    0 -- Notify off, for now. 
  FROM master..sysprocesses sp 
  LEFT OUTER JOIN #HostList hl 
    ON sp.hostname = hl.Host 
  WHERE sp.loginame = @LoginName 
    AND hl.Host IS NULL 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to insert sysprocesses to #UsrLoginScan: ' +  char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
-- Compare the list to the last scan, and flag new unauthorized sessions to notify. 
BEGIN TRY 
  UPDATE uls 
  SET uls.Notify = 1 
  FROM #UsrLoginScan uls 
  LEFT OUTER JOIN dbo.UsrLoginLastScan ulls 
    ON  uls.LoginName = ulls.LoginName 
    AND uls.Host = ulls.Host 
    AND uls.DBName = ulls.DBName 
    AND uls.SPID = ulls.SPID 
    AND uls.LoginDT = ulls.LoginDT 
 -- AND uls.BatchDT = ulls.BatchDT -- Not considering this a new instance for now. 
  WHERE ulls.LoginName IS NULL 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to set notify flag on new unauthorized sessions: ' +  char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
-- Copy the information to the Last Scan and History tables. 
BEGIN TRY 
  INSERT INTO dbo.UsrLoginHist 
  SELECT 
    @LogDate, 
    LoginName, 
    Host, 
    DBName, 
    SPID, 
    LoginDT, 
    BatchDT 
  FROM #UsrLoginScan 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to insert to UsrLoginHist: ' +  char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
TRUNCATE TABLE UsrLoginLastScan 
  
BEGIN TRY 
  INSERT INTO UsrLoginLastScan 
  SELECT 
    LoginName, 
    Host, 
    DBName, 
    SPID, 
    LoginDT, 
    BatchDT 
  FROM #UsrLoginScan 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to replace UsrLoginLastScan: ' +  char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
-- If there were unauthorized sessions set for notify, set the error message. 
SELECT @LoginCount = COUNT(*) 
FROM #UsrLoginScan 
WHERE Notify = 1 
  
IF (SELECT @LoginCount) > 0 
BEGIN 
  SELECT @ErrMsg = N'WARNING: ' + CAST(@LoginCount AS nvarchar(2)) + 
                   N' unauthorized connections by ' + @LoginName + 
                   N' on server ' + CAST(@@ServerName AS nvarchar(30)) + 
                   N' at ' + CONVERT(nvarchar(30),@LogDate,121) + '.' 
  RAISERROR (@ErrMsg,16,1) WITH LOG 
END 
  
-- Now purge old records for this ID. 
BEGIN TRY 
  DELETE FROM UsrLoginHist 
  WHERE LoginName = @LoginName 
    AND LogDT < @PurgeDate 
END TRY 
BEGIN CATCH 
  SELECT @ErrMsg = N'Unable to purge old records from UsrScanHist: ' +  char(13) + 
  ERROR_MESSAGE() 
  GOTO ExitProc -- FATAL ERROR 
END CATCH 
  
-- Set the @ErrMsg back to NULL so the process can exit successfully. 
SELECT @ErrMsg = NULL 
  
-- Exit, raising error if neccessary. 
ExitProc: 
IF (SELECT @ErrMsg) IS NOT NULL 
BEGIN 
  RAISERROR(@ErrMsg,16,1) 
  RETURN(-1) 
END 
ELSE 
  RETURN(0) 

Any comments, suggestions or other help is always appreciated. Thanks.