December 12, 2005 at 10:01 am
How can I find when a particular SQL Login has last logged into SQL Server?
December 12, 2005 at 10:57 am
Either run a trace to audit log ins and log outs or create application functionality for particular application.
Regards,Yelena Varsha
December 12, 2005 at 11:02 am
Thanks Yelena. Is there a tutorial to quickly setup a trace to audit log ins and log outs?
December 14, 2005 at 3:34 pm
I am not sure. I used BOL articles:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_trace_1nci.asp
and a couple of ones it refers to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_8h49.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_1c4p.asp
The last one lists events so you can use only events that you need. Here is an example of Login monitoring. You may select to use less events or fields. This code is pretty much commented, I did it once for myself.
After you run a trace you will use Query Analyzer to select from the trace. Use your path.
select DatabaseId, Hostname, LoginName, NTUserName, NTDomainName, ApplicationName, StartTime, EndTime
from ::fn_trace_gettable('E:\TraceFiles\Trace1.trc',default)
------------------------------------------------------------
--Script Starts Here:
--This is a trace to monitor user access
--@RC return value. See a list of values in BOL sp_trace_create topic
--@TraceID assigned by SQL Server
--@options=2 means Trace_File_Rollover
--@tracefile=N'E:\TraceFiles\Trace1' the location of the trace file
--@maxfilesize=20 (MB, default = 5)
--@stoptime=getdate()+5 stop 5 days from the start time
--
--Declaring variables
--
DECLARE @rc int, @Trace_ID int, @onswitch BIT, @stop datetime, @maxsize bigint
--
--Creating a trace
--
select @stop=getdate()+10
select @maxsize=5
EXEC @rc = sp_trace_create @traceid=@Trace_ID output,
@options=2, @tracefile=N'E:\TraceFiles\Trace1',@maxfilesize=@maxsize, @stoptime = @stop
--
--Returning Trace_ID
--
SELECT TraceID = @Trace_ID, RC = @rc
SELECT @onswitch = 1
--
-- Adding events to the trace
--
-- @on specifies if the event is on in this trace
--
-- @eventid = Is the ID of the event to turn on. event_id is int, with no default
-- 14 Login
-- 15 Logout
-- 20 Login Failed
-- 12 Batch Completed
--
-- @columnid=Is the ID of the column to be added for the event. column_id is int, with no default
-- 1 Text Data
-- 3 DatabaseID
-- 6 NTUserName
-- 7 NTDomainName
-- 8 ClientHostName
-- 9 ClientProcessID
-- 10 ApplicationName
-- 11 SQLSecurityLoginName
-- 14 StartTime
-- 15 EndTime
-- 35 DatabaseName
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 1, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 3, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 6, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_6 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 7, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_7 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 8, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_8 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 9, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_9 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 10, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_10 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 11, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_11 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 14, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_14 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 15, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_15 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =14, @columnid = 35, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_35 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 1, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 3, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 6, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_6 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 7, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_7 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 8, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_8 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 9, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_9 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 10, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_10 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 11, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_11 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 14, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_14 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 15, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_15 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =15, @columnid = 35, @on=@onswitch
SELECT TraceID = @Trace_ID, RC15_35 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 1, @on=@onswitch
SELECT TraceID = @Trace_ID, RC14_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 3, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 6, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_6 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 7, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_7 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 8, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_8 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 9, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_9 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 10, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_10 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 11, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_11 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 14, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_14 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 15, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_15 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =20, @columnid = 35, @on=@onswitch
SELECT TraceID = @Trace_ID, RC20_35 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 1, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_1 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 3, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 6, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_6 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 7, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_7 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 8, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_8 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 9, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_9 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 10, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_10 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 11, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_11 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 12, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 13, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_3 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 14, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_14 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 15, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_15 = @rc
exec @rc=sp_trace_setevent @traceid=@Trace_ID, @eventid =12, @columnid = 35, @on=@onswitch
SELECT TraceID = @Trace_ID, RC12_35 = @rc
-- Set the Filter
-- @columnid = 3 DatabaseID
-- @logical_operator = 0 AND = 1 OR
-- @comparison_operator = 0 EQUAL
-- @value = 8 and @value = 11 for MyDB1 and MyDB2
--
EXEC @rc = sp_trace_setfilter @traceid =@Trace_ID , @columnid = 3, @logical_operator = 0, @comparison_operator = 0, @value = 8
SELECT TraceID = @Trace_ID, RC_Filter_1 = @rc
EXEC @rc = sp_trace_setfilter @traceid =@Trace_ID , @columnid = 3, @logical_operator = 1, @comparison_operator = 0, @value = 11
SELECT TraceID = @Trace_ID, RC_Filter_1 = @rc
-- Start Trace (status 1 = start)
exec @rc = sp_trace_setstatus @Trace_ID, 1
SELECT Trace_Start_Status=@RC
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply