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

    Thx.

    MG

    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?

    MG

    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.

    CREATE EVENT SESSION [Login] ON SERVER 
    ADD EVENT sqlserver.login(
        ACTION(sqlserver.database_name,sqlserver.username))
    ADD TARGET package0.event_file(SET filename=N'c:\temp\Login')
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO
    
    
    

    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