none
请问如何删除数据库审计,数据库是SQLSERVER 2008,谢谢! RRS feed

  • 问题

  • 创建数据库审计的方法是采用如下的方法创建的,没找到如何删除这些审计

    https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

    2018年7月12日 7:57

答案

  • 很奇怪,既然是看过文章应该会注意到下面这个部分,按照这个做即可。(这个是停用,如果要删除,将相关的触发器,表,数据库删除即可)

    Note that when you create a DDL Trigger, just like a DML Trigger, it is enabled and will start working immediately. To disable it, you can run the following code:

    USE YourDatabase;
    GO
    
    DISABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;


    family as water

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月12日 14:05
  • 把这个触发器DDLTrigger_Sample 删除 

    ALTER TRIGGER DDLTrigger_Sample
        ON DATABASE
        FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
            ALTER_SCHEMA
    AS
    BEGIN


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 0:03
  • USE YourDatabase;
    GO

    DROP TRIGGER DDLTrigger_Sample
        ON DATABASE

    GO

    最后把存储审计的数据训也删除即可

    DROP DATABASE AuditDB

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 1:11
  • Hi Tim-2009,

    用这个query把所有trigger列出来,然后看一下哪个是你创建的。

    SELECT 
         sysobjects.name AS trigger_name 
        ,USER_NAME(sysobjects.uid) AS trigger_owner 
        ,s.name AS table_schema 
        ,OBJECT_NAME(parent_obj) AS table_name 
        ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
        ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
        ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
        ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
        ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
        ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
    FROM sysobjects 
    
    INNER JOIN sysusers 
        ON sysobjects.uid = sysusers.uid 
    
    INNER JOIN sys.tables t 
        ON sysobjects.parent_obj = t.object_id 
    
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id 
    
    WHERE sysobjects.type = 'TR' 

    Best Regards,

    Tiege


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 1:47
    版主
  • 在数据库触发器中找到了,也谢谢各位!
    • 已标记为答案 Tim-2009 2018年7月16日 2:46
    2018年7月16日 2:46

全部回复

  • 很奇怪,既然是看过文章应该会注意到下面这个部分,按照这个做即可。(这个是停用,如果要删除,将相关的触发器,表,数据库删除即可)

    Note that when you create a DDL Trigger, just like a DML Trigger, it is enabled and will start working immediately. To disable it, you can run the following code:

    USE YourDatabase;
    GO
    
    DISABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;


    family as water

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月12日 14:05
  • 把这个触发器DDLTrigger_Sample 删除 

    ALTER TRIGGER DDLTrigger_Sample
        ON DATABASE
        FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
            ALTER_SCHEMA
    AS
    BEGIN


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 0:03
  • USE YourDatabase;
    GO

    DROP TRIGGER DDLTrigger_Sample
        ON DATABASE

    GO

    最后把存储审计的数据训也删除即可

    DROP DATABASE AuditDB

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 1:11
  • USE YourDatabase;
    GO

    DROP TRIGGER DDLTrigger_Sample
        ON DATABASE

    GO

    最后把存储审计的数据训也删除即可

    DROP DATABASE AuditDB


    不记得trigger叫啥名字了,请问怎么查呢?
    2018年7月13日 1:18
  • Hi Tim-2009,

    用这个query把所有trigger列出来,然后看一下哪个是你创建的。

    SELECT 
         sysobjects.name AS trigger_name 
        ,USER_NAME(sysobjects.uid) AS trigger_owner 
        ,s.name AS table_schema 
        ,OBJECT_NAME(parent_obj) AS table_name 
        ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
        ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
        ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
        ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
        ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
        ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
    FROM sysobjects 
    
    INNER JOIN sysusers 
        ON sysobjects.uid = sysusers.uid 
    
    INNER JOIN sys.tables t 
        ON sysobjects.parent_obj = t.object_id 
    
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id 
    
    WHERE sysobjects.type = 'TR' 

    Best Regards,

    Tiege


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Tim-2009 2018年7月16日 2:47
    2018年7月13日 1:47
    版主
  • Hi Tim-2009,

    用这个query把所有trigger列出来,然后看一下哪个是你创建的。

    SELECT 
         sysobjects.name AS trigger_name 
        ,USER_NAME(sysobjects.uid) AS trigger_owner 
        ,s.name AS table_schema 
        ,OBJECT_NAME(parent_obj) AS table_name 
        ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
        ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
        ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
        ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
        ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
        ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
    FROM sysobjects 
    
    INNER JOIN sysusers 
        ON sysobjects.uid = sysusers.uid 
    
    INNER JOIN sys.tables t 
        ON sysobjects.parent_obj = t.object_id 
    
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id 
    
    WHERE sysobjects.type = 'TR' 

    Best Regards,

    Tiege


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    请问怎么查询结果为空?
    2018年7月13日 2:01
  • 查了一下被加trigger的数据库没有TR这个类型

    2018年7月13日 2:10
  • 在数据库触发器中找到了,也谢谢各位!
    • 已标记为答案 Tim-2009 2018年7月16日 2:46
    2018年7月16日 2:46