January 29, 2009 at 1:45 pm
We have SQL Server 2000 and 2005. I would like to know if there is an option I can turn on to monitor logins to our Server/DBs. Also, is there a way to monitor or check 'who logged in' by running sql code?
Thanks, Kevin
January 30, 2009 at 6:57 am
kevinsql7 (1/29/2009)
We have SQL Server 2000 and 2005. I would like to know if there is an option I can turn on to monitor logins to our Server/DBs. Also, is there a way to monitor or check 'who logged in' by running sql code?Thanks, Kevin
In the server properties on the security tab you can say to any login event, then create warnings in the sqlagent for error code 18456 and 18453. You could then extract the logins from server log using xp_ReadErrorLog.
January 30, 2009 at 7:56 am
never used them myself so dont know how good they are but 2005 has logon triggers.
http://msdn.microsoft.com/en-us/library/bb326598(SQL.90).aspx
January 30, 2009 at 10:58 am
I can't remember where I found this, but I use the following trigger to audit login events. This has the added benefit of writing the events to a table.
CREATE TABLE [admin].[logs](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](100) NULL,
[LoginName] [varchar](100) NULL,
[HostName] [varchar](100) NULL,
[AppName] [varchar](255) NULL,
[Event_Data] [xml] NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [ServerWideLoginLogs]
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO admin.logs (EventTime,EventType,LoginName,HostName,AppName,Event_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@event)
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ServerWideLoginLogs] ON ALL SERVER
January 30, 2009 at 11:04 am
The problem with login triggers is that, if anything gets messed up so that they error out, nobody will be able to log in to the server, including you. So, be very, very careful to put an abundance of error handling into them.
For example, the one presented here will block logins if the table is ever dropped or renamed, or if access to it is ever blocked, or anything like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply