locked
permission auditing RRS feed

  • Question

  • Hi All,

    last week we found that, one database user permission has been revoked. Is their any chance to audit this on sql server. I m planning to set this.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Wednesday, August 1, 2012 5:19 PM

Answers

  • Yes this is very much possible. There are variety of way to achieve this task i.e.

    1. DDL Trigger
    2. Event Notification
    3. AUDIT Specification

    Here is the sample code of the DDL trigger:

    CREATE TABLE AUDIT (EventInfo xml) CREATE TRIGGER ddl_trig_audit_security_events ON ALL DDL_SERVER_SECURITY_EVENTS,DDL_DATABASE_SECURITY_EVENTS FOR CREATE_DATABASE AS INSERT INTO AUDIT (EventInfo) SELECT EVENTDATA()

    GO



    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com


    • Proposed as answer by Basit Farooq Friday, August 3, 2012 3:53 PM
    • Edited by Basit Farooq Friday, August 3, 2012 3:54 PM
    • Marked as answer by Maggie Luo Monday, August 13, 2012 9:35 AM
    Friday, August 3, 2012 3:53 PM

All replies

  • hi,

    The Audit Change Database Owner event class occurs when you use the ALTER AUTHORIZATION statement to change the owner of a database, and the permissions required to do that are checked.

    pls visit :

    http://msdn.microsoft.com/en-us/library/ms178118.aspx


    Ahsan Kabir

    Friday, August 3, 2012 7:16 AM
  • Yes this is very much possible. There are variety of way to achieve this task i.e.

    1. DDL Trigger
    2. Event Notification
    3. AUDIT Specification

    Here is the sample code of the DDL trigger:

    CREATE TABLE AUDIT (EventInfo xml) CREATE TRIGGER ddl_trig_audit_security_events ON ALL DDL_SERVER_SECURITY_EVENTS,DDL_DATABASE_SECURITY_EVENTS FOR CREATE_DATABASE AS INSERT INTO AUDIT (EventInfo) SELECT EVENTDATA()

    GO



    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com


    • Proposed as answer by Basit Farooq Friday, August 3, 2012 3:53 PM
    • Edited by Basit Farooq Friday, August 3, 2012 3:54 PM
    • Marked as answer by Maggie Luo Monday, August 13, 2012 9:35 AM
    Friday, August 3, 2012 3:53 PM