1

I need to configure Database Auditing in SQL Server. I did configure everything but looks like I am not getting expected result. What I need to do. Audit DML operations (Insert,Update,Delete) for few SQL Server AD GROUPS as most of the users are grouped in AD groups so we need to Audit AD Groups not individual users as there are more than 100 users in one AD group and we have several AD groups which we want to Audit. We want to Audit those AD Groups which are having 'SysAdmin' to the Server Instance. We need to know which member/members of these AD groups which are having SysAdmin on server doing any DML operation in the databses or not.

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.

Goutam
  • 21
  • 2
  • Hmm… that is weird. One question and one thing that's worked for me in the past. Are the groups in question explicitly principals in the database? That is, are getting access implicitly by virtue of the group being a member of another group? And the thing that's worked for me in the past is to audit the database role as the principal. In your case, you'd use db_owner (since that seems to be what you're trying to do); I've used public to look for dead schema and it worked like a charm. – Ben Thul Jul 11 '17 at 14:38
  • Hi Ben .. Thanks for your response. Sorry .. I have updated my question a little bit here. Actually all those AD groups are SysAdming of Sql server and we know members are from all those groups are doing some DML operatoin on databases .. we need to get those info. I have added those AD groups as principal name. In the Audit log nothing is logged. – Goutam Jul 11 '17 at 18:52
  • Ben .. Just to clarify once again.. Those AD groups are having sysadmin server role and we want to Audit DML operation of those AD Groups. We want to see if any member of those AD groups are doing any DML operation ? – Goutam Jul 11 '17 at 19:04
  • Indeed. For a database audit, my expectation would be that the audit would be tracking database principals. You should be able to audit on any of the things listed in sys.user_token for that user when their database context is the database under audit. In this case, that should include db_owner (by virtue of membership in sysadmin) – Ben Thul Jul 11 '17 at 19:39
  • What is your server version? I tried to configure the same audit using AD group with 2 members in it, one sysadmin without any mapping to a database and another just a user that is not mapped as an individual account but the group is a user in that database, and it works fine for tracking actions of both of them. I'm on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) – sepupic Jul 12 '17 at 06:43
  • Hi Ben .. I tried to grant db_owner to the database for that AD group . that also not worked. – Goutam Jul 12 '17 at 15:57
  • version is Microsoft SQL Server 2012 (SP3) (KB3072779) -11.0.6020.0 (X64) . I tested with my Windows account which is a member of the AD group which has sysadmin of the server. I just explicitly granted db_owner to my windows account and tested for one insert operation. it is not logged. is that my account is already in the ad group which has 'sa' and again I am adding the account explicitly with db_owner of database is clashing? in database audit configuration I can not add the AD group as principal until I am granting db_owner to the database. as it is not a database principal. – Goutam Jul 12 '17 at 16:12
  • Just to mention here .. I need audit for DML operations for user databases. – Goutam Jul 12 '17 at 16:15

0 Answers0