Post reply

SQL Database Audit

  • February 21, 2018 at 8:36 am

    #344161

    Hi all,

    My question relates to SQL Server audits, in particular the database audit, what I'd like to do is to record all logins to the server and then all activity in a specific database.

    The login bit has been done using a Server audit specification and thats fine, my question relates to the database one.

    I used this;

    CREATE DATABASE AUDIT SPECIFICATION [Log360DB]
    FOR SERVER AUDIT [Log360DBSpec]
    ADD (DELETE ON DATABASE::[GDPRAudit] BY [testuser]),
    ADD (INSERT ON DATABASE::[GDPRAudit] BY [testuser]),
    ADD (SELECT ON DATABASE::[GDPRAudit] BY [testuser]),
    ADD (UPDATE ON DATABASE::[GDPRAudit] BY [testuser])
    WITH (STATE = ON)
    GO

    My issue is this only records activity for the user testuser when in fact I want all users. The issue is I have to specify a user name as far as I can tell.

    So do I have to create a row for each action/user, like

    ADD (UPDATE ON DATABASE::[GDPRAudit] BY [testuser])
    ADD (UPDATE ON DATABASE::[GDPRAudit] BY [someotheruser])

    Or is there a way to say BY ALL?

    Thanks,

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 21, 2018 at 9:08 am

    #1980636

    Use roles. In this case, I can't test, but I think you can track all activity by public. Every user is in public.

  • February 21, 2018 at 9:16 am

    #1980638

    Hi Steve,

    Good idea. I'll give it a try and let you know how it goes.

    Thanks,

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 21, 2018 at 9:37 am

    #1980648

    Steve Jones - SSC Editor - Wednesday, February 21, 2018 9:08 AM

    Use roles. In this case, I can't test, but I think you can track all activity by public. Every user is in public.

    That worked just as needed. Thanks again.

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 22, 2018 at 9:56 am

    #1980796

    Cheers

  • February 22, 2018 at 11:34 am

    #1980820

    NicHopper - Wednesday, February 21, 2018 9:37 AM

    Steve Jones - SSC Editor - Wednesday, February 21, 2018 9:08 AM

    Use roles. In this case, I can't test, but I think you can track all activity by public. Every user is in public.

    That worked just as needed. Thanks again.

    Now all that's left to do is to reserve a couple of Terabytes of disk space to handle all the audit logging and setup meaningful automated partitioning in conjunction with effective "permanent" archiving that isn't really permanent because an individual can ask for a record of themselves to be deleted including deletion from any long term backups, disk files, etc, ad infinitum. 😀  That is unless you can "black box" it all in a totally understandable manner and then you can invoke the article that states if you don't know what your own stuff actually does, you might not have to worry about it. 😛:Whistling::hehe:😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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