Login audit

Question

Login audit

Walshy

Mr or Mrs. 500

Points: 590
More actions
April 29, 2013 at 3:27 am

#277053

Hi,
I've been asked to produce a report on who has accessed a particular database on an SQL server we have. The system (a third party with no internal auditing) uses sql authentication.
Logging on the server is set to record failed and successful login attempts. I can read the log files using xp_readerrorlog but that only gives the the time and name of the user and not the database.
Any one got any ideas on how I can do this report?
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

since the accessing of data is done after the login, the log isn't going to help you.

you would need to add a new level of auditing to capture those events in the future; you will not be able to find out whodunnit /who accessed data from the past.

So you can set something up going forward.

I've done this with a server side DML trace like in this post: http://www.sqlservercentral.com/Forums/FindPost745586.aspx , and you can use Event Notifications, which (i've heard) have a lesser impact than a trace, like this post from Colleen M. Morrow: http://www.sqlservercentral.com/Forums/FindPost1360378.aspx

alternatively the built in 2008 SQL Audit, the Change Data Capture or the Change Tracking features might be useful to you, and the full C2 Level auditing (which is just a server side trace anyway) is another option.

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!

tarun777.pant Grasshopper Points: 18 More actions · Answer 2

To generate the report on who accessed the particular database on an SQL Server as well as to get the details regarding what is the name of the database I can recommend you to try in Lepide's tool. I have tested this tool and it performs these operations excellently. Besides this it also has the quality to give sufficient details for the user who had successful logins and failed logins in the SQL Server.