SQL Server 2005 Track logins by users

Last Post 29 Apr 2008 05:52 AM by SwePeso. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ckim472
New Member
New Member

--
28 Apr 2008 01:01 PM
How can I track the logins for the last 24 hours for a specific database?
Is there a sys. table or do we have to create a DDL trigger for logins?
Thanks!
ckim472
New Member
New Member

--
29 Apr 2008 04:42 AM
In an Ideal world...
I was hoping I could just query a table in the system database for :

select user, database, datetime
from aLoginTable
where datetime of Login in last 24 hours.

I looked around online and couldnt really find anything about a login table I could query.
The only thing I found was information about creating a DDL trigger to capture login
history.

Thanks Gunney.
SwePeso
New Member
New Member

--
29 Apr 2008 05:52 AM
There is a possibility left.

If you have set your SQL Server to log all logins and not jus the unsuccesful ones), you can check in Windows Security Log to see which logins have been made.
ckim472
New Member
New Member

--
29 Apr 2008 09:56 AM
This is what I found online...What do you guys think?


use master
GO
create database Admin_Log
GO
use Admin_Log
GO
create schema [admin]
GO

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)
GO


--===========================================================
use master
GO
create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON

as begin

DECLARE @event XML
SET @event = eventdata()

INSERT INTO Admin_Log.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
ckim472
New Member
New Member

--
02 May 2008 11:43 AM
Only login history for each database. So...

LoginHistoryTable

[User] [Database] [Start datetime] [End datetime]

Nothing critical but was surprised to find there wasn't a sys table that recorded that.

Thanks.

You are not authorized to post a reply.

Acceptable Use Policy