Today I had to investigate some scenarios in Dynamics AX with which I wasn't familiar. This meant that I had to understand what each of my actions was changing on the database. In order to see these changes I've chosen to create SQL Server auditing triggers. This is not a very performant way of detecting these but it works pretty well for this simple scenario.
Here's what I used:
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE [dbo].[Audit](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NULL,
[TableName] [varchar](64) NULL,
[PrimaryKeyField] [varchar](1000) NULL,
[FieldName] [varchar](64) NULL,
[FieldType] [varchar](32) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [smalldatetime] NULL,
[UserName] [varchar](64) NULL
) ON [PRIMARY]
GO
DECLARE @sql1 varchar(8000), @sql2 varchar(8000), @sql3 varchar(8000) ,@TABLE_NAME sysname, @compatible_triggerfields VARCHAR(8000)
SET NOCOUNT ON
-- L I S T A L L T A B L E N A M E S F R O M S C H E M A
-- (EXCEPT Audit and sysdiagrams)
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
AND TABLE_NAME != 'Audit'
AND TABLE_NAME != 'dtproperties'
-- L O O P T R O U G H E V E R Y T A B L E
WHILE @TABLE_NAME IS NOT NULL
BEGIN
-- D R O P E X I S T I N G T R I G G E R B E F O R E C R E A T I O N
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
-- E X C L U D E I N C O M P A T I B L E F I E L D S
SET @compatible_triggerfields = NULL
SELECT @compatible_triggerfields = coalesce(@compatible_triggerfields+'],[','[') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME and DATA_TYPE not in ('text','ntext','image')
SET @compatible_triggerfields = @compatible_triggerfields + ']'
-- D Y N A M I C C R E A T I O N O F A T R I G G E R
SELECT @sql1 =
'
--------------------------------------------
-- --
-- '+ @TABLE_NAME +'
-- --
--------------------------------------------
CREATE TRIGGER [dbo].[' + @TABLE_NAME+ '_ChangeTracking] on [dbo].[' + @TABLE_NAME+ '] for insert, update, delete
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(64) ,
@TableName VARCHAR(64) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000) ,
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(64) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000) ,
@OldValueSQL VARCHAR(128) ,
@NewValueSQL VARCHAR(128) ,
@fieldtype VARCHAR(32) ,
@compatible_triggerfield BIT
--You will need to change @TableName to match the table to be audited
select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
SELECT @UserName = SYSTEM_USER , @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ '' '' + CONVERT(VARCHAR(12), GETDATE(), 114)'
SELECT @sql2 = '
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = ''U''
ELSE
SELECT @Type = ''I''
ELSE
SELECT @Type = ''D''
-- get list of columns
SELECT '+ @compatible_triggerfields +' INTO #ins FROM inserted
SELECT '+ @compatible_triggerfields +' INTO #del FROM deleted
'
SELECT @sql3 = '-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + '' and'', '' on'')
+ '' i.'' + cu.COLUMN_NAME + '' = d.'' + cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = ''PRIMARY KEY''
AND cu.TABLE_NAME = pk.TABLE_NAME
AND cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+''+'','''')
+ ''''''<'' + COLUMN_NAME + ''=''''+convert(varchar(100), coalesce(i.'' + COLUMN_NAME +'',d.'' + COLUMN_NAME + ''))+''''>''''''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = ''PRIMARY KEY''
AND cu.TABLE_NAME = pk.TABLE_NAME
AND cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR(''no PK on table %s'', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield BEGIN SELECT @field = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN (''I'',''D'')
BEGIN
SELECT @fieldname = COLUMN_NAME, @fieldtype = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
IF @fieldtype = ''text'' or @fieldtype = ''ntext'' or @fieldtype = ''image'' SET @compatible_triggerfield = 0 else SET @compatible_triggerfield = 1
SET @OldValueSQL = '',convert(varchar(1000),d.'' + @fieldname + '')''
SET @NewValueSQL = '',convert(varchar(1000),i.'' + @fieldname + '')''
IF @compatible_triggerfield = 0
BEGIN
SET @OldValueSQL = '',''''n/a''''''
SET @NewValueSQL = '',''''n/a''''''
END
SELECT @sql = ''
insert Audit ( Type,
TableName,
PrimaryKeyField,
FieldName,
FieldType,
OldValue,
NewValue,
UpdateDate,
UserName)
select '''''' + @Type + '''''',''''''
+ @TableName + '''''','' + @PKSelect
+ '','''''' + @fieldname + ''''''''
+ '','''''' + @fieldtype + ''''''''
+ @OldValueSQL
+ @NewValueSQL
+ '','''''' + @UpdateDate + ''''''''
+ '','''''' + @UserName + ''''''''
+ '' from #ins i full outer join #del d''
+ @PKCols
IF @compatible_triggerfield = 1
BEGIN
SET @sql = @sql
+ '' where i.'' + @fieldname + '' <> d.'' + @fieldname
+ '' or (i.'' + @fieldname + '' is null and d.''
+ @fieldname
+ '' is not null)''
+ '' or (i.'' + @fieldname + '' is not null and d.''
+ @fieldname
+ '' is null)''
END
EXEC (@sql)
--EXECUTE spWriteStringToFile @sql,''c:\'',''test.txt''
END
END
'
EXEC(@sql1+@sql2+@sql3);
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME > @TABLE_NAME
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
AND TABLE_NAME != 'Audit'
AND TABLE_NAME != 'dtproperties'
END
segunda-feira, 30 de novembro de 2009
Subscrever:
Enviar feedback (Atom)
Nice, thanks for sharing this post that explanation how monitor changes in sql server and i found really good information from http://www.lepide.com/sql-server-audit to track sql server changes and auditing access permission and track all SQL servers in the network to audit all changes from a centralized platform . This tool instantly generates real time alert to sensitive changes and export report in defferent files format.
ResponderEliminar