Audit User Access to a Database

Question

Post reply

Audit User Access to a Database

thunderousity

Default port

Points: 1466
More actions
September 5, 2011 at 8:42 am

#256619

We have been asked by our internal audit for the number of times a specific user/login has accessed a specific database over the past 12 months.
Is this logged anywhere within SQL Server?
Can this be 'Switched on' for future logging?

Viewing 6 posts - 1 through 5 (of 5 total)

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

chris.mcgowan SSCarpal Tunnel Points: 4269 More actions · Answer 1

Classic answer but in this case it is true "it depends". What version(s) of SQL Server are you using?

Thanks

Chris

------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 2

This links may help:

http://technet.microsoft.com/en-us/library/dd277388.aspx#XSLTsection125121120120

http://msdn.microsoft.com/en-us/library/ms175850.aspx

😉

My blog

thunderousity Default port Points: 1466 More actions · Answer 3

This particular database is on 2005.

Following the links provided above by Ignacio A. Salom Rangel I found that luckily the database had Login Auditing switched on for both Successful and Failed Logins.

I managed to stumble upon two articles on how to read the Login Auditing logs from the log file using the undocumented procedure xp_readerrorlog

http://www.sqlservercentral.com/articles/Security/sqlserverauditingpart1/1451/

http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

KevinGurney SSCommitted Points: 1550 More actions · Answer 4

If only logins then login auditing is sufficient.

I believe it is held in the sql logs, which are rotated and a new one created every time sql is restarted, so you may wish to increase the number of retained logs.

If you wish to track the queries sent to the server also, then a profiler job will be required.

---------------------------------------------
If data can screw you, it will; never assume the data are correct.

thunderousity Default port Points: 1466 More actions · Answer 5

I executed the xp_readerrorlog procedure a few times but changed the 1st parameter to read each log file as they only had about 3 months of data per file. I then saved the results in excel (I believe you could put them in table form)

e.g.

EXEC master.dbo.xp_readerrorlog 0, 1, 'Login', '[Login_Name]', NULL, NULL, N'desc'

EXEC master.dbo.xp_readerrorlog 1, 1, 'Login', '[Login_Name]', NULL, NULL, N'desc'

EXEC master.dbo.xp_readerrorlog 2, 1, 'Login', '[Login_Name]', NULL, NULL, N'desc'...

xp_readerrorlog parameters are as follows:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

2. Log file type: 1 or NULL = error log, 2 = SQL Agent log

3. Search string 1: String one you want to search for

4. Search string 2: String two you want to search for to further refine the results

5. Search from start time

6. Search to end time

7. Sort order for results: N'asc' = ascending, N'desc' = descending