Audit Table For User, Login, Logout

[edsuk]

I have been asked to record user activity for a specific database, to track who logged in/out and when.

I want to write this data to an 'Audit Table'.

Being new to SQL Server (2005) could someone assist and point me in the right direction. Is there a sp that can be used to capture this info or a system table?

Any help would be very much appreciated.

Thanks

Mark...

 

[mrdenny]

Setup a trace to run which monitors login and logout.

That or enable auditing within the database and it will log every users attempted and successful login to the database to the errorlog.

It will make your errorlog hard to read if people log in and out a lot.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[edsuk]

thanks denny

but I need to ge the data into a table, so that the information can be viewed by management in the application they use.

They want to be able to click onto a user in a combo box and view the users log-in/out date/times. This is not a problem as long as I can get the data into my new table in the SQL database.

Basically I was hoping to use either a trigger or sp to populate this table with the relevant data when a user logs on/off - which is why I ws hoping that maybe a system table exists which logs user activity.

Cheers

Mark...

 

[mrdenny]

You can setill setup a SQL Trace. Script out a SQL Profiler trace and set it up to run as a job when ever the server starts. Have the trace log the data into a table on the server. This way you can query the table. (I'm pretty sure you can log a trace to a table without using profiler.) If not I know that you can log it to a file, then come up with a way to load it to the table from the file.

The only table which holds this info would be the sysprocesses table, but you would have to do some major processing to get this to work correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog