SQL Server Login/Logout Audit

Question

Post reply

SQL Server Login/Logout Audit

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 2, 2012 at 10:00 am

#260070

I know i can audit the time when a user logged in and also for failed logins. My goal is log when a user logins and log's out of sql server. Is that possible? Any thoughts?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

durai nagarajan

SSCoach

Points: 15778

August 3, 2012 at 6:26 am

#1521341

i too want to know this...

Regards
Durai Nagarajan

Ant-Green SSC Guru Points: 113509 More actions · Answer 2

Profiler or a trace for the audit logout event.

Or build a custom script which checks sys.dm_exec_sessions for a spid and username and run it every so oftern, when it appears you can log it as the login, when its gone then its logged out.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

anthony.green (8/3/2012)
Profiler or a trace for the audit logout event.
Or build a custom script which checks sys.dm_exec_sessions for a spid and username and run it every so oftern, when it appears you can log it as the login, when its gone then its logged out.

Thanks. Do you have such script handy?

Lowell SSC Guru Points: 323518 More actions · Answer 4

I do.

this script adds a stored procedure, which i assume you would put in master:

linky for sp_AddMyLogonTrace.txt

After running the script to create the procedure, if you run

EXEC sp_AddMyLogonTrace

it will either create (or stop & drop ) the Login trace,and also creates a view for the trace to make it easier to review:

a simple SELECT * FROM sp_LogonTrace

makes it fast to look at what I'm after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 5

the advantage of the procedure is if i decide i want one running all the time, i can set the procedure to execute on server startup, so the trace gets restarted every time the server bounces.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

Lowell (8/3/2012)
the advantage of the procedure is if i decide i want one running all the time, i can set the procedure to execute on server startup, so the trace gets restarted every time the server bounces.

Thanks. I am little reluctant to have trace running all the time, i was thinking to use this dmv sys.dm_exec_sessions to get the same info. thoughts?

Lowell SSC Guru Points: 323518 More actions · Answer 7

Id say that a server side trace has an incedibly small footprint because its not capturing much; plus you get better tracing than watxhing a dmv and infering a logoff.

Test both methods yourself so you can be sure. I personally have 3 traces running on my servers. A logon trace, a dml and a ddl, but i admit h the server only has 100 or so simultaneous usersmax.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

Lowell (8/3/2012)
Id say that a server side trace has an incedibly small footprint because its not capturing much; plus you get better tracing than watxhing a dmv and infering a logoff.
Test both methods yourself so you can be sure. I personally have 3 traces running on my servers. A logon trace, a dml and a ddl, but i admit h the server only has 100 or so simultaneous usersmax.

first time i ran the sproc, it dropped the trace and view. Second when i executed i got this error message

'Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line 1

Procedure expects parameter '@value' of type 'int'.

'

Lowell SSC Guru Points: 323518 More actions · Answer 9

my fault; in 2008 this line raises an error:

I updated the script to correct the issue.

exec sp_trace_setfilter @traceidout,12,0,4, 50 -- AND SPID >= N'50'

changed to:

exec sp_trace_setfilter @traceidout,12,0,4, 50 -- AND SPID >= 50

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 10

Lowell (8/6/2012)
my fault; in 2008 this line raises an error:
I updated the script to correct the issue.
exec sp_trace_setfilter @traceidout,12,0,4, 50 -- AND SPID >= N'50'
changed to:
exec sp_trace_setfilter @traceidout,12,0,4, 50 -- AND SPID >= 50

Thanks for prompt response. I just posted another question on forum, but even you might know. Why do i see an entry to audit logout right after i login ? and then i see another entry when i actually logout.