[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-03-23
8
Medium Priority
?
344 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:WJENorris
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 28424971
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
 
LVL 29

Expert Comment

by:QPR
ID: 28425090
this article speaks of some new auditing options with SQL 2008
http://msdn.microsoft.com/en-us/library/dd392015.aspx
0
 

Author Comment

by:WJENorris
ID: 28427234
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 29

Expert Comment

by:QPR
ID: 28427398
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
 

Author Comment

by:WJENorris
ID: 28430982
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
 
LVL 29

Accepted Solution

by:
QPR earned 1000 total points
ID: 28431087
From the date the audit is put in place
0
 
LVL 29

Expert Comment

by:QPR
ID: 28431276
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
 

Author Closing Comment

by:WJENorris
ID: 31706326
Many thanks for your help. Will

ps QPR ???
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question