locked
Auditing on a group (Group has only sysadmin users) RRS feed

  • Question

  • Hi Folks

    Here I have been configured auditing to capture the events (DML + DDL) which are performing by sysadmin users on my sql server 2008.

    Enable auditing at server level for DDL activities. Here I have been configured audit action types are DATABASE_OBJECT_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Now I am not able to select particular "Principal Name" to capture events for a Group. When I click on browse button nothing displayed.

    As same as I have enabled auditing at database level to capture DML events for the group user. But the DML events are not capturing for this group (Group has all sysadmin users).

    My queries are below

    How I can configured server level audit for DDL events for a particular group (Group has all the sys admin logins)???

    Is it possiable to capture DML at database level for a group user???

    Please clarify my quries..

    Thanks you so much if you provide your valuable thoughts...

    Regards

    Sureddy

     

     

     

     

     


    Sureddy
    Tuesday, September 13, 2011 8:49 AM

Answers

  • Hi

    You Need To Add dbo In Principal. and you will be able trace DML of sys admin.


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Marked as answer by Chreddy S Wednesday, September 14, 2011 3:36 PM
    • Unmarked as answer by Chreddy S Wednesday, September 14, 2011 3:36 PM
    • Proposed as answer by Peja Tao Thursday, September 15, 2011 3:04 AM
    • Marked as answer by Chreddy S Thursday, September 15, 2011 12:43 PM
    Wednesday, September 14, 2011 2:52 PM
  • Kuldeep Bisht,

    You are rock... It helped to my requirement...

    Here is a small thing along with sysadmin activities auditing recording DML activities performing by sql server agent account.

    Please help me how can we filter these sql server agent account activities...

    Thanks for you reply


    Regards; Sureddy; Database Administrator
    • Marked as answer by Chreddy S Thursday, September 15, 2011 7:05 AM
    Thursday, September 15, 2011 7:05 AM
  • Thanks for your continuous support...

    I will talk with system security folks and get back to you.

     

     


    Regards; Sureddy; Database Administrator
    • Marked as answer by Chreddy S Thursday, September 15, 2011 12:42 PM
    Thursday, September 15, 2011 9:07 AM

All replies

  • I think you are trying server level Audit, Try Database Level Audit You Will Get Access To Principals and Individual Objects.

    See Following Image


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Tuesday, September 13, 2011 4:09 PM
  • Hi Kuldeep Bisht,

    Thanks for your quick reply

    I have enabled server level audit to capture DDL events this is working okay. As you said I have enabled database level audit for DML events for individual sysadmin user.

    Now the issue is auditing not capturing DML events for this individual sysadmin user. I have tried with non-sysadmin user where auditing can able to record DML events successfully.

    Here DML events are not recording for sysadmin user. when I set "Principal name" to "Public" then it is recording DDL and DML for all the users (including sysadmins). But the requirement is we have to capture DDL and DML events performing by sysadmins only.

    We have also tried with a group user where all the sysadmins are in that group. even though we did not get DML events.

    Thank you

    Regards

    Sureddy


    Sureddy
    Wednesday, September 14, 2011 6:59 AM
  • Hi

    You Can Do It. When you make a user "sysadmin" then user has all databases access , you need not to give individual database access.

    Well I should say it default behavior or bug in SQL Server After having sysadmin right those user does not appear in Database Audit Principal.

    They Will only Appear if you go to  Login Property -->User Mapping Option of that user  and you will have to check each and every database(Even If The User Is Sysadmin).

    See Following Image

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Wednesday, September 14, 2011 8:10 AM
  • Kuldeep Bisht,

    Yes, you said right. we have tested it in the similar way, but it is not recording DML events.

    This issue only happening with sysadmin user. If I set "pricipal name" to a non-sysadmin user, then it is recording DML events for that user.

    In my scenario, we have to do audit on 18 sysadmin logins. we have added these 18 to an AD group and created a group user on the database, enabled the auditing. still auditing is not recording DML events for these logins.

    I am wondering if there is any limitations for sysadmins in the auditing. I have gone through MS websites for this, I did not see the limitations.

    Thanks for your quick reply...

    Regards

    Sureddy


    Sureddy
    Wednesday, September 14, 2011 9:57 AM
  • Kuldeep Bisht,

    Any updated??

    Thanks for your response

     


    Regards; Sureddy; Database Administrator
    Wednesday, September 14, 2011 1:21 PM
  • Hi

    You Need To Add dbo In Principal. and you will be able trace DML of sys admin.


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Marked as answer by Chreddy S Wednesday, September 14, 2011 3:36 PM
    • Unmarked as answer by Chreddy S Wednesday, September 14, 2011 3:36 PM
    • Proposed as answer by Peja Tao Thursday, September 15, 2011 3:04 AM
    • Marked as answer by Chreddy S Thursday, September 15, 2011 12:43 PM
    Wednesday, September 14, 2011 2:52 PM
  • Kuldeep Bisht,

    You are rock... It helped to my requirement...

    Here is a small thing along with sysadmin activities auditing recording DML activities performing by sql server agent account.

    Please help me how can we filter these sql server agent account activities...

    Thanks for you reply


    Regards; Sureddy; Database Administrator
    • Marked as answer by Chreddy S Thursday, September 15, 2011 7:05 AM
    Thursday, September 15, 2011 7:05 AM
  • Try This Change File Name to Your Audit FileName

    Select * from sys.fn_get_audit_file('D:\Audit-20110912-231148_B008DB3F-8D5D-45FA-8107-FCBEF851BAFA_0_129603229234600000.sqlaudit',DEFAULT,DEFAULT)
    Where session_server_principal_name='AgentAccountName'
    
    



    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Thursday, September 15, 2011 7:23 AM
  • Kuldeep Bisht,

    Does't we prevent log records those are performed by sql server agent account from writing to sql audit log file ??

    I am asking just for my curiosity...

    If audit is writing only sysadmin activities then there is no scope to grow audit log file size. because sql agent might perform huge amount of dml operations.

    Thank you

     


    Regards; Sureddy; Database Administrator
    Thursday, September 15, 2011 7:38 AM
  • Yes , You Are right. But I don't See Any Option or Policy To Prevent Agent Account (If Its Sysadmin)  From Audit.

    removing agent service account from sysadmin role  can be a option.

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Thursday, September 15, 2011 8:14 AM
  • Thanks for your continuous support...

    I will talk with system security folks and get back to you.

     

     


    Regards; Sureddy; Database Administrator
    • Marked as answer by Chreddy S Thursday, September 15, 2011 12:42 PM
    Thursday, September 15, 2011 9:07 AM
  • Kuldeep Bisht,

    Finally all are cool with this.

    Thanks for your help...

     


    Regards; Sureddy; Database Administrator
    Thursday, September 15, 2011 12:42 PM
  • Kuldeep Bisht

    Is there any way to impliment same for DDL activities for sysadmins only? We did not able to set audit specifications for DATABASE_OBJECT_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP audit action types in both database and server level. Is there any other audit action type apart from the above two?

    Thanks for your greate help


    Regards; Sureddy; Database Administrator
    Thursday, September 15, 2011 2:02 PM
  • Hi,

    Thanks a lot . This Really helped me.

    Sunday, July 3, 2016 10:16 AM
  • Hi,

    There is a problem here.Since we have set the audit on dbo schema it captures all the activities from all users on that particular database.Which is not pleasant.

    I just want to record activities from sysadmin users.


    Sunday, January 1, 2017 6:19 AM