locked
SQL Server Auditing - Database Auditing RRS feed

  • Question

  • Hello All.

    I need to configure Database Auditing in SQL Server. I did configure everything but looks I am not getting expected result. What I need to do. Audit DML operations (Insert,Update,Delete) for few SQL Server AD GROUPS

    which is having db_owner to the database. What I Configured. 1. Audit Action Type : Insert, Update ,Delete 2. Object Class :Database 3. Object Name :Selected the database which I want to Audit 4. Principal Name :The AD Group which I want to Audit for (The SQL Server AD group contains several windows accounts as members) So what I am expecting for this configuration , Whenever a member of that SQL Server AD group executes any DML operation that should logged. But here I am not seeing anything in Audit Logs. Help me here. Thanks in Advance.



    Saturday, June 17, 2017 5:48 PM

All replies

  • See if this works (Example for INSERTS)

    CREATE DATABASE AUDIT SPECIFICATION [Audit_Db_Dbo]
    FOR SERVER AUDIT [Audit_Dbo]
    ADD (INSERT ON OBJECT::[dbo].[tbl] BY [dbo])
    WITH (STATE = ON)



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, June 18, 2017 4:54 AM
  • Thanks for your reply .. but looks like the script is for a single table. I can not specify a single table to audit.. There are more than 1000 tables and I dont know user will use which table to dml operation.
    Sunday, June 18, 2017 4:56 PM
  • You add the audit specification on schema level like this:

    CREATE DATABASE AUDIT SPECIFICATION DBAactive
    FOR SERVER AUDIT DBAActivity
    ADD (INSERT ON SCHEMA::dbo BY db_owner)

    Beware that the audit unfortunately does not audit all users. I ran an INSERT as myself, and I was not audited, because I am member of sysadmin. But when I created another user that I made member of db_owner, the INSERT from that user was audited.

    Sunday, June 18, 2017 6:43 PM
  • Hi Erland .
    
    Thanks for your reply. Is it possible to Audit for AD group.
    
    like we want to do Audit for AD group ... we want to check the activity of the  members of that particual AD group. Ok let tell you more here.. We want to Audit for few AD Groups which got SysAdmin Server role.
    
    This is the Database Audit configuration setting ..
    
    
    1. Audit Action Type : Insert, Update ,Delete
    
    2. Object Class :Database
    
    3. Object Name :Selected the database which I want to Audit
    
    4. Principal Name :The AD Group which I want to Audit for (The SQL Server AD group contains several windows accounts as members)
    
    Is that it is not supporting AD GROUP as Principal ?


    Wednesday, June 28, 2017 4:12 PM
  • Why don't you test?

    I don't have an AD here at home, so I am not in position to test myself.

    Wednesday, June 28, 2017 9:37 PM
  • I did the test. not seeing any members details in the logs, who are in those ad groups. 



    GG


    Thursday, June 29, 2017 3:34 PM
  • Hi Goutam,

    Have you tried to audit the operation of your own login? According to your description, not seeing any details in the log, this can be a permission issue, have you checked the VIEW ANY DEFINITION permission on your account?

    Best Regards,

    Teige


    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.

    Friday, June 30, 2017 9:20 AM
  • Hi Teige .. Yes, I tried to test my id .. as my windows account is a member of an AD Group which got 'SA' to the server. So as per the database audit configuration what I did.. 1. Audit Action Type : Insert, Update ,Delete 2. Object Class : Database 3. Object Name : Selected the database which I want to Audit 4. Principal Name : The AD Group which I want to Audit for (The SQL Server AD group contains

    several windows accounts as members), here I selected the AD group where my

    Widows account is a member.

    So when I am executing DML operation it suppose to logs but I dont see anything there. but If i add my Domain account as Principal name then I can see whaterver dml operation I am

    performing it is being logged. So I am thinking if we add AD group as pricipal it is not working.







    GG

    Friday, June 30, 2017 3:54 PM