SQLSERVER数据审计的存储过程_sqlserver 审计存储过程-CSDN博客

下面是整个脚本，在NORTHWIND数据库中可运行,若需要用在其他数据库中，则将相应的表和触发器改动为需要跟踪的表：
/*
Dynamic Audit Trigger Table and Code
Paul Nielsen www.IsNotNull.com
This sample script adds the dynamic audit trigger to
Northwind Customers and Products table.
Version 1.1 - Aug 6, 2001
*/
USE Northwind
-------------------------------------------------------------
-- Create the table to store the Audit Trail
IF Exists (SELECT * FROM sysobjects WHERE NAME = 'Audit')
DROP TABLE Audit
Go
CREATE TABLE Audit (
AuditID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
AuditDate DATETIME NOT NULL,
SysUser VARCHAR(50) NOT NULL,
Application VARCHAR(50) NOT NULL,
TableName VARCHAR(50)NOT NULL,
Operation CHAR(1) NOT NULL,
PrimaryKey VARCHAR(50) NOT NULL,
-- RowDescription VARCHAR(50) NULL,
SecondaryRow VARCHAR(50) NULL,
[Column] VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NULL,
NewValue VARCHAR(50) NULL
)
GO
-------------------------------------------------------------
-- Create function to simulate the Columns_Updated() value
IF EXISTS (SELECT *
FROM sysobjects
WHERE NAME = 'GenColUpdated')
DROP FUNCTION GenColUpdated
Go
CREATE FUNCTION dbo.GenColUpdated
(@Col INT, @ColTotal INT)
RETURNS INT
AS
BEGIN
-- Copyright 2001 Paul Nielsen
-- This function simulates Columns_Updated()
DECLARE
@ColByte INT,
@ColTotalByte INT,
@ColBit INT
-- Calculate Byte Positions
SET @ColTotalByte = 1 + ((@ColTotal-1) /8)
SET @ColByte = 1 + ((@Col-1)/8)
SET @ColBit = @col - ((@colByte-1) * 8)
-- gen Columns_Updated() value for given column position
RETURN
POWER(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-1)
END
go
-------------------------------------------------------------
-- Create the Dynamic Audit Stored Procedures
IF EXISTS (SELECT * FROM SysObjects WHERE NAME = 'pAudit')
DROP PROC pAudit
Go
CREATE PROCEDURE pAudit (
@Col_Updated VARBINARY(1028),
@TableName VARCHAR(100),
@PrimaryKey SYSNAME)
AS
-- dynamic auto-audit trigger/stored procedure
-- Copyright 2001 Paul Nielsen
SET NoCount ON
DECLARE
@ColTotal INT,
@ColCounter INT,
@ColUpdatedTemp INT,
@ColName SYSNAME,
@BlankString CHAR(1),
@SQLStr NVARCHAR(1000),
@ColNull NVARCHAR(50),
@SysUser NVARCHAR(100),
@ColumnDataType INT,
@IsUpdate BIT,
@tempError INT
SET @SysUser = suser_sname()
SET @BlankString = ''
-- Initialize Col variables
SELECT @ColCounter = 0
SELECT @ColTotal = Count(*)
FROM SysColumns
    JOIN SysObjects
      ON SysColumns.id = SysObjects.id
WHERE SysObjects.name = @TableName
-- Set IsUpdated Flag
IF EXISTS(SELECT * FROM #tempDel)
SELECT @IsUpdate = 1
ELSE
SELECT @IsUpdate = 0
-- Column Updates
WHILE ((SELECT @ColCounter) != @ColTotal)
-- run through some columns
BEGIN
    SELECT @ColCounter = @ColCounter + 1
    SET @ColUpdatedTemp
         = dbo.GenColUpdated(@ColCounter,@ColTotal)
   -- bitwise AND between updated bits
   -- and the selected column bit
IF (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp
    BEGIN
      SET @ColNull = null
      SELECT
          @ColName = SysColumns.[name],
           -- get the column name & Data Type
          @ColumnDataType = SysColumns.xtype
        FROM SysColumns
          JOIN SysObjects
            ON SysColumns.id = SysObjects.id
        WHERE SysObjects.[NAME] = @TableName
          and SysColumns.ColID = @ColCounter
      IF @ColName NOT IN ('Created', 'Modified')
        BEGIN
          -- text columns
          IF @ColumnDataType IN
              ( 175, 239, 99, 231, 35, 231, 98, 167 )
            SET @ColNull = ''''''
          -- numeric + bit columns
          ELSE IF @ColumnDataType IN
             ( 106, 62, 56, 60, 108, 59, 52, 122, 104 )
            SET @ColNull = '0'
          -- date columns
          ELSE IF @ColumnDataType IN ( 61, 58 )
            SET @ColNull = '''1/1/1980'''
          -- uniqueidentifier columns
          ELSE IF @ColumnDataType IN ( 36 )
            SET @ColNull = ''''''

          IF @ColNull IS NOT NULL
            BEGIN
              IF @IsUpdate = 1
SET @SQLStr =
' Insert Audit(TableName, PrimaryKey, SysUser, [Column],'
   +' AuditDate, Application, OldValue, NewValue,Operation)'
   +' Select '''+ @TableName + ''',
   #tempIn.['+ @PrimaryKey + '],
   ''' + @SysUser + ''', ' +
   '''' + @ColName + ''', GetDate(), App_Name(),' +
   ' IsNull(convert(nvarchar(100),
       #tempDel.[' + @ColName + ']),''<null>''), ' +
   ' IsNull(convert(nvarchar(100),
       #tempIn.[' + @ColName +   ']),''<null>''),''U''' +
   ' From #tempIn' +
   ' Join #tempDel' +
   ' On #tempIn.['+ @PrimaryKey + ']
      = #tempDel.['+ @PrimaryKey + ']' +
   ' AND isnull(#tempIn.' + @ColName +   ',' + @ColNull + ')
     != isnull(#tempDel.' + @ColName +   ',' + @ColNull + ')'
   + ' Where Not (#tempIn.[' + @ColName + '] Is Null
     and #tempDel.[' + @ColName + '] Is Null)'

ELSE -- Insert
SET @SQLStr =
' Insert Audit(TableName, PrimaryKey, SysUser, [Column],'
+' AuditDate, Application, OldValue, NewValue,Operation)'
+' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey
+ '], ''' + @SysUser + ''', ' +
'''' + @ColName + ''', GetDate(), App_Name(),' +
' Null, ' +
' IsNull(convert(nvarchar(100),
   #tempIn.[' + @ColName +']),''<null>''),''I''' +
' From #tempIn' +
' Where Not (#tempIn.[' + @ColName + '] Is Null)'
EXEC sp_executesql @SQLStr
SET @TempError = @@Error
IF @TempError <> 0
BEGIN
    -- turn rollback on only if you want a
    -- failure to record audit to cancel
    -- the data modification operation
    -- Rollback
    RAISERROR ('Audit Trail Error', 15, 1)
END
END
END
END
END
RETURN
Go
------------------------------------------------------------
------------------------------------------------------------
-- sample Table Triggers
-- this will need to be added to every table
-- and the Table and Primary Key settings
-- Products trigger
IF EXISTS (SELECT *
FROM sysobjects
WHERE NAME = 'Products_Audit')
DROP TRIGGER Products_Audit
Go
CREATE TRIGGER Products_Audit
ON dbo.Products
AFTER Insert, Update
NOT FOR REPLICATION
AS
-- Dynamic Audit Trail Code Begin
-- (c)2001 Paul Nielsen
DECLARE
@Col_Updated VARBINARY(1028),
@TableName VARCHAR(100),
@PrimaryKey SYSNAME
SET NoCount ON
-- Set up the Audit data
-- set to the table name
SET @TableName = 'Products'
-- set to the column to identify the row
SET @PrimaryKey = 'ProductID'
SET @Col_Updated = Columns_Updated()
SELECT * INTO #TempIn FROM Inserted
SELECT * INTO #TempDel FROM Deleted
-- call the audit stored procedure
EXEC pAudit @Col_Updated, @TableName, @PrimaryKey
Go
-------------------------------------------------------------
-- Customer Trigger
IF EXISTS (SELECT *
FROM SysObjects
WHERE [NAME] = 'Customers_Audit')
DROP TRIGGER Customers_Audit
Go
CREATE TRIGGER Customers_Audit
ON dbo.Customers
AFTER Insert, Update
NOT FOR REPLICATION
AS
-- Dynamic Audit Trail
-- (c)2001 Paul Nielsen
DECLARE
@Col_Updated VARBINARY(1028),
@TableName VARCHAR(100),
@PrimaryKey SYSNAME
SET NoCount ON
SET @TableName = 'Customers'
SET @PrimaryKey = 'CustomerID'
SET @Col_Updated = Columns_Updated()
SELECT * INTO #TempIn FROM Inserted
SELECT * INTO #TempDel FROM Deleted
EXEC pAudit @Col_Updated, @TableName, @PrimaryKey
go
----------------------------------------------------
-- Sample test code
-- test insert
INSERT Products (ProductName, CategoryID)
VALUES('Ye Old Audit Trail', 1)
go
-- test single row updates
UPDATE Products
SET ProductName = 'Audit Test'
WHERE ProductID = 1
go
UPDATE Products
SET ReorderLevel = 3
WHERE ProductID = 1
go
-- test multi-row update
UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 1
go
-- test all columns update
UPDATE Customers
SET
ContactName = 'Phil Senn',
CompanyName = 'AuditTest',
ContactTitle = 'M.P.',
Address = '123 Audit Ln. Pl. Ave. Dr.',
City = 'Hickory',
Region = 'NC',
PostalCode = '12345',
Country = 'US',
Phone = '555 123-4567',
Fax = '555 123-4568'
WHERE CustomerID = 'ALFKI'
go
-- examine raw table
SELECT * FROM Audit
go
-- view Products w/ Audit
SELECT Products.ProductName, Audit.*
FROM Products
JOIN Audit
ON Audit.PrimaryKey = Products.ProductID
WHERE Audit.TableName = 'Products'