locked
powershell for auditing Active directory users logged into SQL SERVER DATABASE. RRS feed

  • Question

  • This is the scenario of my research work : I have twenty active directory accounts  that makes use of a central database (their accounts had been registered with the sql server database). The active directory users, edits and deletes data within the database.

    I want to write a script to monitor sql server event logins scenario for each  Active Directory accounts . I want the script to capture the time the users login into the database, the tables  they edited, and every events associated  with EACH ACTIVE DIRECTORY USER.

    Could someone give me a hint on how to go about it, possible weblinks and sources would do.
    Tuesday, August 29, 2017 2:10 PM

Answers

  • First, there really is no such thing as what you are looking for built into SQL Server.  You have to build it yourself.

    For auditing the users and what they did, you can use a server audit.  This will get the COMMANDS run by a user.

    For auditing the actual previous values and current values of a table, you would need to use CDC or a trigger or something.

    • Proposed as answer by Teige Gao Monday, September 4, 2017 5:39 AM
    • Marked as answer by Olaf HelperMVP Saturday, October 7, 2017 5:54 AM
    Wednesday, August 30, 2017 4:59 PM

All replies

  • Have you considered implementing CDC to achieve your goal instead of powshell?
    About Change Data Capture (SQL Server)

    Is there some reason your requirement calls for using PowerShell, as opposed to a built-in feature with similar capability?

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, August 29, 2017 3:18 PM
  • Just went through the documentation at : https://technet.microsoft.com/en-us/library/cc645937%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    CDC  can not be used  for  SQL SERVER AUDITING OF USERS ACTIVITIES.

    How  do i approach it?

    Wednesday, August 30, 2017 10:18 AM
  • CDC captures data changes in a database (insert/update/delete).

    Specifically, what kind of 'activity' are you wanting to audit? Can you provide examples of what you're talking about to help us gain a better understanding of exactly what you are trying to achieve?

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, August 30, 2017 12:10 PM
  • The main focus rae :

    (1) The Active Directory/system administrator user login details : The time he logged in, operations (activities) in the database table

    (2) BASIC WHO DID WHAT IN A DATABASE TABLE, and his computer login credentials.

    thanks for the correspondence.

     

    Wednesday, August 30, 2017 12:53 PM
  • Ok, in that case, you may want to consider implementing SQL Server Audit feature before going down the path of trying to build a custom auditing solution with PowerShell:
    SQL Server Audit (Database Engine)

    It has the capability to track and answer "who did what and where?" questions.

    HTH, 


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, August 30, 2017 4:24 PM
  • First, there really is no such thing as what you are looking for built into SQL Server.  You have to build it yourself.

    For auditing the users and what they did, you can use a server audit.  This will get the COMMANDS run by a user.

    For auditing the actual previous values and current values of a table, you would need to use CDC or a trigger or something.

    • Proposed as answer by Teige Gao Monday, September 4, 2017 5:39 AM
    • Marked as answer by Olaf HelperMVP Saturday, October 7, 2017 5:54 AM
    Wednesday, August 30, 2017 4:59 PM