Server外键审核/跟踪

Question

我有一个SQL Server 2008 DB，外键神秘地消失了。数据库由许多定制软件、MS查询和访问。在从数据库中添加、修改或删除外键时，我希望设置某种类型的日志/审核。我该怎么做？理想情况下，更改将写入SQL表，并帮助确定是谁进行了更改。

Answer 1

创建表时，可以创建触发器来存储对数据库的更改。全文：http://www.sqlbook.com/sql-server/using-ddl-triggers-in-sql-server-to-audit-database-objects/

CREATE TRIGGER AuditProcChanges
ON DATABASE
FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login) 
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
) 

Answer 2

请查看下面的线程链接以获得帮助。

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0dfea12c-4133-42ff-b6fa-4b0f78cdfb33/audit-foreign-key-constraints?forum=sqldatabaseengine

Answer 3

Stivan在上面的评论中给出了我的问题的答案。如果他把它作为一个答案，我将把它转换为接受的答案。尽管如此，在执行Create/Alter/Drop语句时，我向希望跟踪的DB添加了一个DDL审核表，并添加了一个DB触发器来填充它。我运行的查询如下所示：

USE DBName
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DDLAudit](
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NULL,
    [DatabaseName] [varchar](256) NULL,
    [Event] [nvarchar](100) NULL,
    [ObjectName] [varchar](256) NULL,
    [TSQL] [nvarchar](2000) NULL,
    [Login] [varchar](256) NULL,
 CONSTRAINT [PK_DDLAudit] PRIMARY KEY CLUSTERED 
(
    [RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE TRIGGER AuditProcChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login) 
VALUES
(
GetDate(),
@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)