How to audit domain user access dates and times to an sql server 2008 database

I need to gain specific information on when an SQL SERVER 2008 database is being accessed and by which domain user (ideally dates and times).  The SQL SERVER 2008 is running on at Windows 2003 Server platform.

All help greatly appreciated.  Thanks, Will
WJENorrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
well suser_name() and getDate() will give you the domain user and the date/time of the activity but how you store or retrieve these depends entirely on other factors?
If you want to know who and when data was selected then you are going to have to cater for this after your select statement. You could keep note of the row(s) accessed (and how will again depend on the platform involved) and could then update those rows with the results of the 2 functions suser_name() and getdate()

select field1, filed2, suser_name(), getDate()
from mytable
where something = this

store this somewhere and do an insert to an audit table with the same resultset or update the base tables with the suser_name() and GetDate() values. Doing an update means you'll only get the last data retrieval so you may want to have a seperate audit table where you store only the primary key of the base table, the user and the timestamp
0
QPRCommented:
this article speaks of some new auditing options with SQL 2008
http://msdn.microsoft.com/en-us/library/dd392015.aspx
0
WJENorrisAuthor Commented:
Thanks for these leads -  they look very useful for predefined security audits - and I will use them. BUT do you know of a 'quick and dirty' way to just view database access times by all domain users in the past 2 weeks ?

Thanks again for your help with this. Will
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

QPRCommented:
To my knowledge there is no built in way/data that will audit db access.... unless it's via an application that deals with it. Without giving away specifics, is there something in particular you are looking for?
0
WJENorrisAuthor Commented:
Yes, the times and dates when a couple of specific users have accessed the SQL database in the last couple of weeks. Thanks again for comments. If necessary I will define an audit as you suggest - can this the report be run retrospectively or does it just report from the date when it's defined and onwards ??
0
QPRCommented:
From the date the audit is put in place
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QPRCommented:
There are other ways to skin a cat though. How would they have accessed the data? Management studio? Application? Remote desktop? You may have events in the windows event viewer logging successful access but not much to prove specific access. Had they modified data then you'd have something to go on
0
WJENorrisAuthor Commented:
Many thanks for your help. Will

ps QPR ???
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 7

From novice to tech pro — start learning today.