Hi,

I am asked to produce a report on who has accessed a particular database on an SQL server 2008 R2. 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. 

Any one got any ideas on how I can do this report?

tarun pant (tarun777.pant@gmail.com) writes:
> I am asked to produce a report on who has accessed a particular database
> on an SQL server 2008 R2. 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. 
> 
> Any one got any ideas on how I can do this report?

It depends. If there are several databases on this server, and you are  
asked to produce this report after-the-fact, the answer is "cannot be 
done". All you get in the log is a message like:

   Login succeeded for user 'usr'. Connection made using Windows 
   authentication. [CLIENT: ]

No trace of any database. But if there is a single database, you can try:

   CREATE TABLE #errorlog (logdate datetime2(3) NOT NULL,
                           bywhom  varchar(50)  NOT NULL,
                           msg     varchar(MAX) NOT NULL)

   INSERT #errorlog EXEC xp_readerrorlog

   SELECT * FROM #errorlog WHERE msg LIKE  'Login succeeded for user%'

You will still need some processing to get a useful report.

For the future, it may be better to look at SQL Server Audit, although
this requires Enterprise Edition.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

For getting a simplified details of the users who have successfully logged in on the SQL Server to access the particular database, I can suggest you a third party tool named LepideAuditor for SQL Server which worked for me excellently in the similar situation. Besides this the tool also has the ability to tell who, what, when and where of all the changes made in the properties and permissions of the SQL Server and database objects.