| Author |
Messages |
|
Ian Konda
 |
| 09/11/2001 4:29 AM |
 Quote
 Reply
 Alert
|
| I am trying to capture the login information (BOTH NT AND SQL) to a table. Is there any way to do that ?.
I used SQL PROFILER, but i want to capture 24 hours a day,365 days . The information should include "USERNAME","DATABASE NAME","LOGIN TIME". This job should run as background. |
|
|
|
|
Chip Andrews
|
| 01/01/2001 12:00 AM |
Quote
Reply
Alert
|
| Sure - you can use the sp_trace procedures to log the data as in the following:
alter procedure sp_mytrace
as
declare @tid int
exec sp_trace_create @traceid = @tid OUTPUT , @options = 1 , @tracefile = N'c:\trace.txt'
exec sp_trace_setevent @traceid = @tid, @eventid =14, @columnid = 1, @on =1
exec sp_trace_setevent @traceid = @tid, @eventid =15, @columnid = 1, @on =1
exec sp_trace_setstatus @traceid = @tid, @status = 1
go
exec sp_procoption 'sp_mytrace', 'startup', 'true'
Sorry I didn't have time to test this but using my example and touching it up with Books Online you should be in business. Make sure to include the additional columns you are interested in such as NT Login Name etc...
Chip
|
|
|
|
|
Ian Konda
|
| 01/01/2001 12:00 AM |
Quote
Reply
Alert
|
| Hi Chip,
Thanks for your reply.
I would like to know the details to do it on SQL 7.0 and 6.5 versions.
Thanks,
|
|
|
|
|
Chip Andrews
|
| 01/01/2001 12:00 AM |
Quote
Reply
Alert
|
| Actually, I don't have access to a SQL Server 7.0 machine right now (much less a SQL 6.5 box) but this should all be possible using the older tracing extended stored procs. See Books Online in SQL Server 2000 for a mapping between the SQL Server 2000 commands and the 7.0 commands by searching for "Creating and Managing Traces and Templates".
Here is a sample of the mappings:
SQL 7.0 SQL 2000
----------------------------------------------------
xp_trace_geteventclassrequired fn_trace_geteventinfo
xp_trace_getqueuecreateinfo fn_trace_geteventinfo
xp_trace_getqueueproperties fn_trace_geteventinfo
xp_trace_getqueuecreateinfo fn_trace_getinfo
xp_trace_getqueuedestination fn_trace_getinfo
xp_trace_getqueueproperties fn_trace_getinfo
xp_trace_addnewqueue sp_trace_create
xp_trace_setqueuecreateinfo sp_trace_create
xp_trace_setqueuedestination sp_trace_create
xp_trace_generate_event sp_trace_generateevent
xp_trace_addnewqueue sp_trace_setevent
xp_trace_eventclassrequired sp_trace_setevent
xp_trace_seteventclassrequired sp_trace_setevent
xp_trace_destroyqueue sp_trace_setstatus
xp_trace_pausequeue sp_trace_setstatus
xp_trace_restartqueue sp_trace_setstatus
xp_trace_startconsumer sp_trace_setstatus
xp_trace_getappfilter fn_trace_getfilterinfo
xp_trace_getconnectionidfilter fn_trace_getfilterinfo
xp_trace_getcpufilter fn_trace_getfilterinfo
xp_trace_getdbIdfilter fn_trace_getfilterinfo
xp_trace_getdurationfilter fn_trace_getfilterinfo
xp_trace_geteventfilter fn_trace_getfilterinfo
xp_trace_gethostfilter fn_trace_getfilterinfo
xp_trace_gethpIdfilter fn_trace_getfilterinfo
xp_trace_getIndIdfilter fn_trace_getfilterinfo
xp_trace_getntdmfilter fn_trace_getfilterinfo
xp_trace_getntnmfilter fn_trace_getfilterinfo
xp_trace_getobjidfilter fn_trace_getfilterinfo
xp_trace_getreadfilter fn_trace_getfilterinfo
xp_trace_getserverfilter fn_trace_getfilterinfo
xp_trace_getseverityfilter fn_trace_getfilterinfo
xp_trace_getspIdfilter fn_trace_getfilterinfo
xp_trace_getsysobjectsfilter fn_trace_getfilterinfo
xp_trace_gettextfilter fn_trace_getfilterinfo
xp_trace_getuserfilter fn_trace_getfilterinfo
xp_trace_getwritefilter fn_trace_getfilterinfo
xp_trace_setappfilter sp_trace_setfilter
xp_trace_setconnectionidfilter sp_trace_setfilter
xp_trace_setcpufilter sp_trace_setfilter
xp_trace_setdbIdfilter sp_trace_setfilter
xp_trace_setdurationfilter sp_trace_setfilter
xp_trace_seteventfilter sp_trace_setfilter
xp_trace_sethostfilter sp_trace_setfilter
xp_trace_sethpIdfilter sp_trace_setfilter
xp_trace_setIndIdfilter sp_trace_setfilter
xp_trace_setntdmfilter sp_trace_setfilter
xp_trace_setntnmfilter sp_trace_setfilter
xp_trace_setobjidfilter sp_trace_setfilter
xp_trace_setreadfilter sp_trace_setfilter
xp_trace_setserverfilter sp_trace_setfilter
xp_trace_setseverityfilter sp_trace_setfilter
xp_trace_setspIdfilter sp_trace_setfilter
xp_trace_setsysobjectsfilter sp_trace_setfilter
xp_trace_settextfilter sp_trace_setfilter
xp_trace_setuserfilter sp_trace_setfilter
xp_trace_setwritefilter sp_trace_setfilter
|
|
|
|
|
|