Posts

Showing posts from July 17, 2012

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

Image
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 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------