如何在SQL表中获取审计跟踪 [英] How to get audit trail in SQL table

问题描述

我有一个sql存储过程，用于获取我的sql数据库中的所有表，也在我的结果中排除用户成员资格和系统图表，工作正常。我的挑战是我想创建另一个存储过程，我将在运行第一个存储过程时获取的表上列出每个表，并为每个存储过程创建审计跟踪，以便我可以看到谁在做什么，何时。我希望这个结果的审计跟踪表包含这个字段

UserId，tablename，更改内容，日期时间。



你能不能请协助



我尝试了什么：



这是我试过的br />

 USE [MISA] 
 GO 
 / ******对象：StoredProcedure [dbo]。[sp_ListAuditTables]脚本日期：14/02 / 2018 14:10 ****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
  -  ========= ==================================== 
  - 作者：<作者，姓名> ; 
  - 创建日期：<创建日期,,> 
  - 说明：< Description ,,> 
  -  ============================================ = 
 ALTER PROCEDURE [dbo]。[sp_ListAuditTables] 
  - 在这里添加存储过程的参数
  - < @Param1，sysname，@ p1> < Datatype_For_Param1 ,, int> =< Default_Value_For_Param1，，0> ;, 
  - < @ Param2，sysname，@ p2> < Datatype_For_Param2 ,, int> =< Default_Value_For_Param2，，0> 
 AS 
 BEGIN 
  - 添加SET NOCOUNT ON以防止
的额外结果集 - 干扰SELECT语句。 
 SET NOCOUNT ON; 
 
  - 在此插入过程语句
 
 --SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE'和TABLE_NAME不喜欢'ASP％'和TABLE_NAME不喜欢'SYS％'
 --ORDER BY TABLE_NAME 
 --SELECT'exec [sp_GenerateAuditTrail] @ TableName ='''+ TABLE_NAME +''''FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE'
 --ORDER BY TABLE_NAME 
 
 SELECT'exec [sp_GenerateAuditTrail] @ TableName ='''+ TABLE_NAME +''''FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE'和TABLE_NAME不喜欢'ASP％'和TABLE_NAME不喜欢'SYS％'
 ORDER BY TABLE_NAME 
结束

带[ sp_GenerateAuditTrail]

解决方案

您无法在事件发生后创建审计跟踪 - 您必须捕获正在发生的更改。



您可以使用几种技术 - 这一点le讨论其中一些

创建一个成功的审计策略您的SQL Server数据库 [ ^ ]



我们在（很多）旧版本的SQL上使用的一种技术是在我们想要审计的表上创建副本 记录到另一个数据库。



我不能强调首先让你的策略正确的重要性 - 如果你直接潜入，这很容易与你失控

I have a sql stored procedure that am using to get all the table in my sql database am also excluding user membership and system diagram tables in my results that is working fine. my challenge is I want to create another stored procedure where I will take each table on the list on tables that I got when am running the first store procedure and create the audit trail for each so that I can see who was doing what, when. I want to have an audit trail table for this results with this fields
UserId, tablename, what change, datetime.

can you please assist

What I have tried:

this what I tried

USE [MISA]
GO
/****** Object:  StoredProcedure [dbo].[sp_ListAuditTables]    Script Date: 14/02/2018 14:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ListAuditTables] 
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

--SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE 'ASP%' AND TABLE_NAME NOT LIKE 'SYS%'
--ORDER BY TABLE_NAME 
	--SELECT 'exec [sp_GenerateAuditTrail] @TableName=''' + TABLE_NAME + '''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
	--ORDER BY TABLE_NAME

		SELECT 'exec [sp_GenerateAuditTrail] @TableName=''' + TABLE_NAME + '''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE 'ASP%' AND TABLE_NAME NOT LIKE 'SYS%'
	ORDER BY TABLE_NAME
END

with a [sp_GenerateAuditTrail]

解决方案

You can't create an audit trail "after the event" - you have to capture the changes happening.

There are several techniques you can use - this article discusses some of them
Creating a successful auditing strategy for your SQL Server databases[^]

A technique we used on a (much) older version of SQL was to have triggers on the tables we wanted auditing that wrote "copy" records to another database.

I can't emphasise enough the importance of getting your strategy right first - this can easily run away with you if you just dive straight in.

这篇关于如何在SQL表中获取审计跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！