SQL Audit - Not Showing Database Name RRS feed

  • Question

  • Fellow SQLers,

    I have setup SQL Audit at the server level.

    I enabled Database Logout Group and Logout_Group.  Also Enabled Successful_Login

    I can watch my sql user id come in and log out.

    What I am using is SSMS, connecting to a sql instance and db with a sql user id/password.

    When I click on the DB that the sql user id DOES have access to:

    1 - I can never see the database name in the event log for the login

    2 - I can not see the db name when they log out.

    IMHO, this should be working.

    Any suggestions would be helpful.



    Monday, July 1, 2019 4:38 PM

All replies

  • Server audits do not record the database name even if the user has a default database.

    The database column will be populated if you have a database audit and the user does an event which is audited, like deleting from a table, but not logging out of a database (unless it is a contained database).

    Monday, July 1, 2019 7:31 PM
  • Hi Hillary,

    Thanks for info. So, I would believe this would also work like you describe with a Server audit that includes a db audit event inside of it?


    Tuesday, July 2, 2019 12:00 PM
  • No, the server audit does not populate the database_name column, the database audit does. These are seperate audit events and don't overlap.

    Note that this extended event might give you what you are looking for.

    ADD EVENT sqlserver.login(
    ADD TARGET package0.event_file(SET filename=N'c:\temp\Login')

    Tuesday, July 2, 2019 3:04 PM
  • HI Hillary,

    Many thanks.

    1 - Let me rephrase my statement.

    I am using the built-in SQL Audit method. I am picking a server level audit - not db level audit.

    Inside that Server Level Audit, if I pick a DB Auditing EVENT (ie. Database_Object_Change_Group), then that  will show me the dB name - if it fires - for any db. I am just trying to say that I do not have to have a DB Audit Session - it can be done with a Server Audit Session using specific DB events.

    2 - Yes, thank for the snippet. I am aware of building a custom event that pulls in the other info thru ACTION. I am trying to just use SQL Audit.

    Thanks MG

    Tuesday, July 2, 2019 3:47 PM