Microsoft SQL Server 2005/2008:Create LOGON Trigger for User Level Auditing

1.Create Table for Insert the Audit Values.

USE [master]
GO

/****** Object:  Table [dbo].[ServerLogonHistory]    Script Date: 07/17/2012 12:22:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ServerLogonHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Login_User] [varchar](100) NULL,
    [Database_User] [varchar](50) NULL,
    [SPID] [int] NULL,
    [LogonTime] [datetime] NULL,
    [Host_Address] [varchar](100) NULL,
    [Last_Program] [varchar](max) NULL,
    [Database_Used] [varchar](30) NULL,
    [LogOffTime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2.Create SERVER Trigger to Audit Login Details

/****** Object:  DdlTrigger [Trg_LogonEntry]    Script Date: 07/17/2012 10:23:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/******************************************************************************/
/* *
* Name         : $Archive: Trg_LogonEntry $
* Version      : $Revision: 1
* Last Updated : $Modtime:  2008-10-15
* Created By           : $Author: Bhavesh Thakkar
* ModIFied By          : $Author: Bhavesh Thakkar
* Script               : This procedures collect information of login.It is for Admin Purpose
* Input:
*       
* Output: Nothing
* Version Info:
*   No.   Author                Date                            Comments
*   ---   ------                ----                            --------
*   1 *   Bhavesh Thakkar        2012-07-16                     Logon Trigger
*/
/*****************************************************************************/

ALTER TRIGGER [Trg_LogonEntry]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @SYSTEM_USER varchar(100)

DECLARE @HOST_NAME varchar(100)

DECLARE @spid int

SET @SYSTEM_USER=SYSTEM_USER

SET @HOST_NAME=HOST_NAME()

SET @spid=@@spid

--SA User only Allow only on SERVER

IF @SYSTEM_USER='SA' AND @HOST_NAME='SERVER'

INSERT INTO [master].[dbo].[ServerLogonHistory]
           ([Login_User]
           ,[Database_User]
           ,[SPID]
           ,[LogonTime]
           ,[Host_Address]
           ,[Last_Program]
           ,[Database_Used]
           ,[LogOffTime])
     VALUES
           (@SYSTEM_USER
           ,User
           ,@@spid
           ,GETDATE()
           ,@HOST_NAME
           ,null
           ,null
           ,null)
          

--When SA user Access outside server

ELSE IF @SYSTEM_USER='SA' AND @HOST_NAME<>'SERVER'
     

         BEGIN

              ROLLBACK;

        END
  

--Normal Level Entry of User..

 
ELSE

INSERT INTO [master].[dbo].[ServerLogonHistory]
           ([Login_User]
           ,[Database_User]
           ,[SPID]
           ,[LogonTime]
           ,[Host_Address]
           ,[Last_Program]
           ,[Database_Used]
           ,[LogOffTime])
     VALUES
           (@SYSTEM_USER
           ,User
           ,@@spid
           ,GETDATE()
           ,@HOST_NAME
           ,null
           ,null
           ,null)


END 

About Me