locked
SQL Server Auditing : Database Auditing for Windows Groups (AD Groups) RRS feed

  • Question

  • Hello All.

    I have been trying to get a solution for this topic..

    actually I have already posted this several times but still I didn't receive any solution.

    What I am looking .In our Database environment there more than 1000 users , these users are Windows users

    and these users are added as members in  several Windows AD Groups based on the DB access requirements.

    There are few AD groups which are having Sysadmin server role.

    Now the requirements is we need to configure DATABASE AUDIT for those AD groups which are having 'SysAdmin'

    Server Role.We need to audit for DML operation (Insert, Update,Delete) for all those AD groups

    which are having 'SysAdmin' Server Role. Please let me know what should be the Database Audit configuration

    details.I have already tested in different way but I am getting any correct result.



    GG



    Monday, July 17, 2017 8:18 PM

Answers

  • Hi GG,

    I’ll assume that you are still using the same configuration as your previous thread, so in the database audit specification:

    >>1. Audit Action Type : Insert, Update ,Delete
    This seems fine.
    >>2. Object Class :Database
    This is also fine.
    >>3. Object Name :Selected the database which I want to Audit
    All good here.
    >>4. Principal Name :The AD Group which I want to Audit for (The SQL Server AD group contains several windows accounts as members)
    Supposedly, you should only be able to pick database user/roles here. As you mentioned these Windows Groups are members of sysadmin server role, in this case, choose dbo here to monitor sysadmin activities.


    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, July 18, 2017 7:28 AM

All replies

  • Hi GG,

    I’ll assume that you are still using the same configuration as your previous thread, so in the database audit specification:

    >>1. Audit Action Type : Insert, Update ,Delete
    This seems fine.
    >>2. Object Class :Database
    This is also fine.
    >>3. Object Name :Selected the database which I want to Audit
    All good here.
    >>4. Principal Name :The AD Group which I want to Audit for (The SQL Server AD group contains several windows accounts as members)
    Supposedly, you should only be able to pick database user/roles here. As you mentioned these Windows Groups are members of sysadmin server role, in this case, choose dbo here to monitor sysadmin activities.


    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, July 18, 2017 7:28 AM
  • Hello Lin .. Thanks for you answer. You are correct, I was supposed to pick database user/roles as

    Principal Name , So what I did, explicitly I have granted db_owner to all those AD groups

    which are having 'sysadmin' SERVER ROLE, so I was able to pickup the AD groups

    in the principal name tab .. But still, I didn't get expected result. I am the member of one of the AD groups which got sysadmin server role.

    I am executing DML operation in the database but I can not see my Windows account in

    the Audit logs with my current configuraiton. Today I did change as per your suggestion and looks like I am getting expected results.

    I will test more. hope i am getting the results. Thanks, Lin.



    Tuesday, July 18, 2017 3:45 PM
  • Hi Lin ..
    
    I have another situation. where I need to audit for a 
    specific user defined database role.That database role is
     supposed to assign to a couple of Windows AD Group.
    
    How can I setup database auditing for that specific 
    Database role so that any user or Windows AD Group which 
    are having that DB role could be audited?
    
    
    Advance thanks..
    
    Goutam


    Wednesday, November 1, 2017 5:42 PM
  • You would do it something like this:

    USE [WideWorldImporters]
    
    GO
    
    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20171101-134514]
    FOR SERVER AUDIT [Audit-20170718-140456]
    ADD (DELETE ON OBJECT::[Sales].[CustomerTransactions] BY [Plains Sales])
    
    GO
    
    

    Here is am auditing deletes on Sales.CustomerTransactions by the database role Plains Sales. If a windows AD group is added to the role, you will achieve the auditing you are looking for.


    Wednesday, November 1, 2017 5:47 PM
  • It is not going to work even if you create a new database role or a user and assign it to the Login, until the database principal is set to dbo in the database audit specification.
    Monday, September 2, 2019 10:07 PM