locked
SQL Server database audit RRS feed

  • Question

  • Hi 

    I  required to set up Database database logins, update (DLL operations) objects  by users,  How to enable and generate . Any one can guide me on this would me much appreciated.  

    thansk

    Sunday, January 14, 2018 10:11 PM

All replies

  • Hi Ashwan,

    Refer following link

    http://www.sqlservergeeks.com/sql-server-how-to-query-for-ddl-changes-from-the-default-trace-when-no-ddl-auditing-has-been-configured-on-the-database/


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, January 14, 2018 10:24 PM
  • Hi ashwan,

    >>I  required to set up Database database logins, update (DLL operations) objects  by users,  How to enable and generate .

    For login events, please create a server audit specification for SUCCESSFUL_LOGIN_GROUP. For update operations, please create a database audit specification for UPDATE actions. Here’s a sample script(using AdventureWorks2014 database):
    USE [master];
    GO
    
    CREATE SERVER AUDIT [TESTAUDIT] ----Create audit
    TO FILE
    (
        FILEPATH = N'C:\',
        MAXSIZE = 0MB,
        MAX_ROLLOVER_FILES = 2147483647,
        RESERVE_DISK_SPACE = OFF
    )
    WITH
    (
        QUEUE_DELAY = 1000,
        ON_FAILURE = CONTINUE
    );
    ALTER SERVER AUDIT [TESTAUDIT]
    WITH
    (
        STATE = ON
    );
    GO
    
    CREATE SERVER AUDIT SPECIFICATION [TESTSERVERAUDITSPEC] ----Create audit spec for login
    FOR SERVER AUDIT [TESTAUDIT]
        ADD
        (SUCCESSFUL_LOGIN_GROUP);
    GO
    
    ALTER SERVER AUDIT SPECIFICATION [TESTSERVERAUDITSPEC] WITH (STATE = ON);
    
    USE [AdventureWorks2014];
    GO
    
    CREATE DATABASE AUDIT SPECIFICATION [TESTAUDITSPEC] ----Create audit spec for UPDATE.
    FOR SERVER AUDIT [TESTAUDIT]
        ADD
        (UPDATE ON DATABASE::[AdventureWorks2014] BY [public]);
    GO
    
    ALTER DATABASE AUDIT SPECIFICATION [TESTAUDITSPEC] WITH (STATE = ON);

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, January 15, 2018 2:30 AM
  • Hi Lin Thanks heaps on our reply. 

    1. Can you explain change/update/delete on a database, where  they recorded . which table. What info will record on each update,delete,insert. 

    2. I  require to audit insert  and delete as well on database objects.  I have 20-30 Database on each instance. 

    in that situation can we use one database to audit all the databases insert/update/delete?

    3. How to do specific objects rather all the objects?

    4. commands is correct ?

     How to add  insert /delete. Please confirm 

    ALTER DATABASE AUDIT SPECIFICATION [TESTAUDITSPEC] 
    FOR SERVER AUDIT [TESTAUDIT]
        ADD
        (INSERT ON DATABASE::[AdventureWorks2014] BY [public]);
    GO

    ALTER DATABASE AUDIT SPECIFICATION [TESTAUDITSPEC] 
    FOR SERVER AUDIT [TESTAUDIT]
        ADD
        (DELETE ON DATABASE::[AdventureWorks2014] BY [public]);
    GO


    4. how to turn off all the audit

    Thanks Heaps!!!!

    • Edited by ashwan Monday, January 15, 2018 8:10 PM
    Monday, January 15, 2018 8:02 PM
  • Hi ashwan,

    >>Can you explain change/update/delete on a database, where  they recorded . which table.

    They are recorded in the location that you specifies in CREATE SERVER AUDIT statement. It can be an audit file, Windows Application Log, or Windows Security Log.

    >>What info will record on each update,delete,insert.

    Please refer to the picture below.


    >>I  require to audit insert  and delete as well on database objects.  I have 20-30 Database on each instance. in that situation can we use one database to audit all the databases insert/update/delete?

    You’ll have to create database audit specification for each database. You may have to create multiple server audits for each audit specification so they don’t mix together.

    >>3. How to do specific objects rather all the objects?

    That requires SQL Server 2012 and later. For example, if you need to audit only Person.Address table, you should add the filter below to the server audit.

    USE [master]
    GO
    
    ALTER SERVER AUDIT [TESTAUDIT] ----Disable audit so we can alter it.
    WITH
    (
        STATE = OFF
    );
    GO
    
    ALTER SERVER AUDIT [TESTAUDIT]
    
    WHERE (OBJECT_NAME = 'Address' AND SCHEMA_NAME = 'Person') ----Audit only Person.Address
    OR (action_id = 1329876565) ----Also audit UNDO actions.
    
    GO
    
    ALTER SERVER AUDIT [TESTAUDIT] ----Bring the audit back online.
    WITH
    (
        STATE = ON
    );
    GO
    


    >>4. commands is correct ?

    Yes.

    >>4. how to turn off all the audit

    Turn them off one by one, with ALTER SERVER AUDIT statement, or use the script in this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 16, 2018 9:12 AM