locked
Audits For SYSAdmin Ids RRS feed

  • Question

  • Hi,

    Can pls anyone tell me how i can implement audits for  a particular sysadmin role? I'm not able to do it for a particular table. I have bussiness requirement wherein i have to monitor the insert update and selects statements being run by a sysadmin role member on a particular table.

    Regards

    Rojit 


    Rojit
    Tuesday, November 30, 2010 10:22 AM

Answers

  •    One limitation that you will face when trying to have different audit levels for members of sysadmin server role is that when any member of sysadmin connects to any database, they will always be mapped to user “dbo”.

       If I understand your problem, you may be facing trouble distinguishing one particular sysadmin from other principals when defining an AUDIT SPECIFICATION in SQL Server 2008, which takes a user name as a parameter, correct? If this is indeed the case, you should be able to audit the access on the table for any principal, and then filter  the results based on the server_principal_id column of the audit records.

        If what you are trying to avoid is explicitly audit access to the table by this particular principal and no one else, the situation becomes a lot more tricky. A quick-and-dirty workaround for sysadmin is granting CONTROL SERVER and create an explicit user on the database.

       I would like to remind you that granting CONTROL SERVER is for all practical purposes equivalent to sysadmin, and therefore it is against the least-privilege principle. The adversary has too much power that can be abused, and unless you monitor all activity from this particular principal, he may find a way to bypass the existing audit definitions.

       I hope this information helps,

      -Raul garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, December 1, 2010 2:40 AM

All replies

  • You can do this with a trigger on the table. Then use the function suser_sname() checking the user name as required and if a match is found insert into an audit table including datetime of operation and operation type etc.

    You can modify the following example and add your " if suser_sname() = 'sa' " clause...

    http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

     

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Tuesday, November 30, 2010 10:44 AM
  • Of course a member of the sysadmin fixed server role can disable the trigger, and/or delete items from the audit table.

    You didn't say what version of SQL Server you were using. If you are using SQL Server 2008 Enterprise Edition, use SQL Server auditing. The sysadmin can still reconfigure the audit, but not without leaving a record of that in the logs.  And if you protect the logs (and export them to secure locations), the SQL Server sysadmin can't fiddle with them.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, November 30, 2010 4:45 PM
  •    One limitation that you will face when trying to have different audit levels for members of sysadmin server role is that when any member of sysadmin connects to any database, they will always be mapped to user “dbo”.

       If I understand your problem, you may be facing trouble distinguishing one particular sysadmin from other principals when defining an AUDIT SPECIFICATION in SQL Server 2008, which takes a user name as a parameter, correct? If this is indeed the case, you should be able to audit the access on the table for any principal, and then filter  the results based on the server_principal_id column of the audit records.

        If what you are trying to avoid is explicitly audit access to the table by this particular principal and no one else, the situation becomes a lot more tricky. A quick-and-dirty workaround for sysadmin is granting CONTROL SERVER and create an explicit user on the database.

       I would like to remind you that granting CONTROL SERVER is for all practical purposes equivalent to sysadmin, and therefore it is against the least-privilege principle. The adversary has too much power that can be abused, and unless you monitor all activity from this particular principal, he may find a way to bypass the existing audit definitions.

       I hope this information helps,

      -Raul garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, December 1, 2010 2:40 AM