Post reply

Audit User Access to a Database

  • 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?

  • September 5, 2011 at 10:07 am

    #1377089

    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
    ------------------------

  • September 5, 2011 at 11:16 am

    #1377114

    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

  • September 6, 2011 at 6:32 am

    #1377340

    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://qa.sqlservercentral.com/articles/Security/sqlserverauditingpart1/1451/

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

  • September 6, 2011 at 7:51 am

    #1377413

    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.

  • September 6, 2011 at 8:12 am

    #1377426

    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

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

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