/
AutoAudit 3.30a.sql
6422 lines (5575 loc) · 317 KB
/
AutoAudit 3.30a.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
--**************************************************************--
--Please read completely and test on your database. --
--AutoAudit makes several changes to your tables. --
--Adding AutoAudit to your tables will impact performance. --
--Set the variables below to your requirements before executing.--
--**************************************************************--
set nocount on
--USE AutoAudit -- edit for your database
--*******************************************************
-- VARIABLE DECLARATIONS
--*******************************************************
declare @AuditSchema varchar(50),
@ViewSchema varchar(50),
@Version varchar(5),
@OptimizeForAudit bit,
@DetailedMigrationCheck bit,
@CreatedColumnName sysname,
@CreatedByColumnName sysname,
@ModifiedColumnName sysname,
@ModifiedByColumnName sysname,
@RowVersionColumnName sysname,
@RebuildTriggersAfterInstall bit,
@WithLogFlag bit,
@DateStyle varchar(3),
@ViewPrefix varchar(10),
@UDFPrefix varchar(10),
@RowHistoryViewSuffix varchar(20),
@DeletedViewSuffix varchar(20),
@RowHistoryFunctionSuffix varchar(20),
@TableRecoveryFunctionSuffix varchar(20)
--*******************************************************
-- VARIABLE INITIALIZATION
--*******************************************************
Set @AuditSchema = 'Audit' --This is the schema to use for the AutoAudit objects. Edit for your database
Set @ViewSchema = '<TableSchema>' --This is the schema to use for the AutoAudit base table views. Edit for your database.
--<TableSchema> = the _RowHistory and _Deleted views have the same schema as the base table.
Set @Version = '3.30a' --leave this unless you are making changes to this script
Set @OptimizeForAudit = 0 --@OptimizeForAudit = 0 creates an index to speed up views
--(and slows down AutoAudit), @OptimizeForAudit = 1 keeps AutoAudit 10% faster
--but the reporting views are slower.
Set @RebuildTriggersAfterInstall = 1 --@RebuildTriggersAfterInstall = 1 launches pAutoAuditRebuildAll after
--this script has completed and AutoAudit has been updated
--@RebuildTriggersAfterInstall = 0 runs this script to update AutoAudit but does NOT
--execute pAutoAuditRebuildAll. This may cause problems with your existing AutoAudit
--triggers views and UDFs.
Set @DetailedMigrationCheck = 0 --This is only applicable if you are upgrading your AutoAudit environment
--from version 2.00h to 3.20. The detailed verification could take several minutes to
--complete.
--0 = quick check with rowcount only, 1 = detailed record verification
Set @WithLogFlag = 0 --This flag determines if the "With Log" function is included in the raierror
--statements or not
--this is added because some DBA's may not have rights to write to the Windows log
--0 = exclude "with log", 1 = include "with log"
--set DDL column names
--*** make sure these DO NOT require quotename() (no spaces, special characters etc.)
--*** IF YOU ARE UPGRADING FROM AUTOAUDIT 2.X set the column names to Created, CreatedBy, Modified, ModifiedBy and RowVersion
-- otherwise the upgrade process will add new columns.
-- YOU CAN CHANGE THE NAMES LATER IN THE BASE TABLES AND THE AUTOAUDITSETTINGS TABLE.
Set @CreatedColumnName = 'AutoAudit_CreatedDate'
Set @CreatedByColumnName = 'AutoAudit_CreatedBy'
Set @ModifiedColumnName = 'AutoAudit_ModifiedDate'
Set @ModifiedByColumnName = 'AutoAudit_ModifiedBy'
Set @RowVersionColumnName = 'AutoAudit_RowVersion'
Set @DateStyle = '121' --this variable identifies the date style you wish to use when inserting data into
--the AuditHeader table. It is recommended you only use a style that provides full
--datetime precision with century. These are the tested and allowed choices.
-- 113 : 26 Nov 2013 13:20:54:553
-- 121 : 2013-11-26 13:22:55.170
--Set object prefixes and suffixes
Set @ViewPrefix = 'v' --User configurable - sets the PREFIX for _RowHistory, _Deleted views
Set @UDFPrefix = '' --User configurable - sets the PREFIX for _RowHistory, _TableRecovery functions
Set @RowHistoryViewSuffix = '_RowHistory' --User configurable - sets the suffix for "_RowHistory" views
Set @DeletedViewSuffix = '_Deleted' --User configurable - sets the suffix for "_Deleted" views
Set @RowHistoryFunctionSuffix = '_RowHistory' --User configurable - sets the suffix for "_RowHistory" functions
Set @TableRecoveryFunctionSuffix = '_TableRecovery' --User configurable - sets the suffix for "_TableRecovery" functions
--*******************************************************
-- END OF VARIABLE INITIALIZATION
--*******************************************************
/*
PLEASE READ NOTES COMPLETELY AND TEST ON YOUR DATABASE.
AUTOAUDIT MAKES SEVERAL CHANGES TO YOUR TABLES.
----------------------------------
AutoAudit script
for SQL Server 2005, 2008, 2008R2, 2012
(c) 2007-2013 Paul Nielsen Consulting, inc.
www.sqlserverbible.com
AutoAudit.codeplex.com
Created by Paul Nielsen
Coded by Paul Nielsen and John Sigouin
December 2013
Version 3.30a
----------------------------------
executing this script will add the following
objects to your database:
Tables:
- <AuditSchema>.AuditHeader (new for 3.00)
- <AuditSchema>.AuditDetail (new for 3.00)
- <AuditSchema>.AuditHeaderArchive (new for 3.00)
- <AuditSchema>.AuditDetailArchive (new for 3.00)
- <AuditSchema>.AuditSettings (new for 3.00)
- <AuditSchema>.AuditBaseTables (new for 3.00)
- <AuditSchema>.AuditAllExclusions (new for 3.00)
- <AuditSchema>.SchemaAudit
Stored Procedures:
- <AuditSchema>.pAutoAudit
- <AuditSchema>.pAutoAutitDrop
- <AuditSchema>.pAutoAuditAll
- <AuditSchema>.pAutoAuditDropAll
- <AuditSchema>.pAutoAuditArchive (new for 3.00)
- <AuditSchema>.pAutoAuditRebuild (new for 3.00)
- <AuditSchema>.pAutoAuditRebuildAll (new for 3.00)
Views:
- <AuditSchema>.vAudit view (new for 3.00)
- <AuditSchema>.vAuditArchive view (new for 3.00)
- <AuditSchema>.vAuditAll view (new for 3.00)
- <AuditSchema>.vAuditHeaderAll view (new for 3.00)
- <AuditSchema>.vAuditDetailAll view (new for 3.00)
Database DDL Trigger:
- SchemaAuditDDLTrigger DDL Trigger
(on database for DDL_DATABASE_LEVEL_EVENTS)
----------------------------------
***************************************************************************
***************************************************************************
Important: The 2.00h Audit table is being replaced by a AuditHeader and
AuditDetail table in version 3.x.
If you are currently using AutoAudit version 2.00h, running this
script will automatically create the new tables and migrate all
of your existing Audit data into the AuditHeader and AuditDetail
tables.
Also, all of your existing AutoAudit base table triggers will be
rebuild such that at the end of this installation, the system
will continue to audit as it used to but save the data to the
new tables.
Your current Audit table will be renamed to LegacyAudit_Migrated
and your current SchemaAudit table will be renamed to
dbo.LegacySchemaAudit_Migrated.
After verifying the installation and migration of your existing
Audit data to the new table structure, you should drop the
LegacyAudit_Migrated and LegacySchemaAudit_Migrated tables.
The new vAudit view created during the installation produces a
recordset identical to what is stored in your current Audit table.
****************************************************************
I am confident the upgrade process works correctly, but because
of possible differences in your installation compared to the
2.00h baseline installation, it is strongly recommended you
backup your database before running this script.
****************************************************************
***************************************************************************
***************************************************************************
Features (default behaviors):
Code-gens triggers to records all inserts, updates, and deletes
into a common generic audit table structure.
on insert: Records insert event in Audit tables (AuditHeader and AuditDetail)
including who made the insert, when, from what application and workstation.
The row's Created and CreatedBy columns also
reflect the user context.
on update: Records update events in the Audit tables including
who, when, from where, and the before and after values.
The row's Modified and ModifiedBy columns also store the basic
audit data. The update also increments the row's RowVersion column.
On delete: All the final values are written to the audit tables
while this permits undeleting rows, it is performance intensive
when deleting a large number of rows on a wide table.
----------------------------------
Limitations:
Does not audit changes of columns of these data types:
text, ntext, image, geography, xml, binary, varbinary, timestamp,
rowversion
Adding AutoAudit triggers to a table will impact performance,
potentially doubling or tripling the normal DML execution times.
The width of the table increases the impact of the AutoAudit
triggers during updates.
AutoAudit database object description
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditHeader Table (new for 3.00)
This table is inserted with one row everytime one record is inserted,
updated or deleted in a table that has been setup to use the
AutoAudit system.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditDetail Table (new for 3.00)
This table is related to AuditHeader and is inserted with one row
for each column that is changed during an insert or update
operation and for each column during a delete operation.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditHeaderArchive Table (new for 3.00)
This table contains all the rows that originated in the AuditHeader
table but that have been selected to be archived based on the
archival timeframes processed by the
<AuditSchema>.pAutoAuditArchive procedure.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditDetailArchive Table (new for 3.00)
This table contains all the rows that originated in the AuditDetail
table but that have been selected to be archived based on the
archival timeframes processed by the
<AuditSchema>.pAutoAuditArchive procedure.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.SchemaAudit Table (modified for 3.00)
This table contains one row for each database DDL event that is
captured by the SchemaAuditDDLTrigger database trigger.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditSettings Table (new for 3.00)
This table contains a small number of rows that are used to persist
some important settings and parameters that are needed by the
AutoAudit system.
Important:
After running this script to install AutoAudit, please review
the User configurable settings and configure them to your
preferences.
Here's a list of entries in the AuditSettings table:
SettingName: AuditSchema
SettingValue: Audit (default)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.
SettingName: Schema for _RowHistory _TableRecovery and _Deleted objects
SettingValue: <TableSchema> (default)
AdditionalInfo: User configurable - Schema AutoAudit uses for
_RowHistory, _TableRecovery and _Deleted objects.
Valid entries can be an existing schema or <TableSchema>.
The default is <TableSchema>. When <TableSchema>
is used, the schema of the AutoAudit views for
with match the schema of each base table.
SettingName: Version
SettingValue: 3.00 (current value)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.
SettingName: SchemaAuditDDLTrigger Enabled Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. 0 = DDL trigger disabled, 1 = DDL
trigger enabled.
SettingName: Archive Audit data older than (days)
SettingValue: 30 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. Audit data older than this number of
days will be moved to the archive tables when the
pAutoAuditArchive stored procedure is executed.
SettingName: Delete Audit data older than (days)
SettingValue: 365 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. Audit data older than this number of
days will be deleted permanently when the
pAutoAuditArchive stored procedure is executed.
SettingName: RowHistory View Scope
SettingValue: Active (default)
AdditionalInfo: User configurable - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Determines
source of data when _RowHistory views are created.
Valid entries are: "Active", "Archive", "All".
SettingName: Deleted View Scope
SettingValue: Active (default)
AdditionalInfo: User configurable - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Determines
source of data when _Deleted views are created.
Valid entries are: "Active", "Archive", "All".
SettingName: Default _RowHistory view Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _RowHistory view is not created,
1 = _RowHistory view is created.
SettingName: Default _RowHistory function Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _RowHistory function is not created,
1 = _RowHistory function is created.
SettingName: Default _TableRecovery function Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _TableRecovery function is not created,
1 = _TableRecovery function is created.
SettingName: Default _Deleted view Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _Deleted view is not created,
1 = _Deleted view is created.
SettingName: Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag
SettingValue: 1 (default)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.
SettingName: Audit Trigger Debug Flag
SettingValue: 0 (default)
AdditionalInfo: User configurable
0 = Debug information (The trigger name and nest level) in returned
by the AutoAudit Insert, Update and Delete triggers.
1 = Debug information is not returned
SettingName: Add Extended Properties Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = Extended properties are not added.
1 = Extended properties are not added on DDL columns under the
MS_Decription name
SettingName: CreatedColumnName
SettingValue: AutoAudit_Created (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record creation date" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.
SettingName: CreatedByColumnName
SettingValue: AutoAudit_CreatedBy (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record created by" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.
SettingName: ModifiedColumnName
SettingValue: AutoAudit_Modified (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record last modifocation date" entry
by AutoAudit when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.
SettingName: ModifiedByColumnName
SettingValue: AutoAudit_ModifiedBy (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record last modified by" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.
SettingName: RowVersionColumnName
SettingValue: AutoAudit_RowVersion (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record verion number" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.
SettingName: ViewPrefix
SettingValue: v (default)
AdditionalInfo: User configurable (default = "v") - Must execute pAutoAuditRebuild(All) or
pAutoAudit(All) to apply change. Sets the prefix to use for the _RowHistory
and _Deleted views.
SettingName: DateStyle
SettingValue: 121 (default)
AdditionalInfo: System setting added by AutoAudit installation SQL script. Do not change
manually in the table. You can re-run the AutoAudit installation script to
change this setting.
SettingName: UDFPrefix
SettingValue: '' (default)
AdditionalInfo: User configurable (default = "") - Must execute pAutoAuditRebuild(All) or
pAutoAudit(All) to apply change. Sets the prefix to use for the _RowHistory
and _TableRecovery views.
SettingName: RowHistoryViewSuffix
SettingValue: _RowHistory (default)
AdditionalInfo: User configurable (default = "_RowHistory") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix
to use for the _RowHistory views.
SettingName: DeletedViewSuffix
SettingValue: _Deleted (default)
AdditionalInfo: User configurable (default = "_Deleted") - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Sets the suffix to use for the _Deleted
views.
SettingName: RowHistoryFunctionSuffix
SettingValue: _RowHistory (default)
AdditionalInfo: User configurable (default = "_RowHistory") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix to
use for the _RowHistory functions.
SettingName: TableRecoveryFunctionSuffix
SettingValue: _TableRecovery (default)
AdditionalInfo: User configurable (default = "_TableRecovery") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix to
use for the _TableRecovery functions.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditBaseTables Table (new for 3.00)
This table contains one row for each of the base tables that was
setup to use the AutoAudit system. The data in this table can
be useful for a DBA who wants to review which base tables are
setup for Auto
Changes to the AutoAudit configuration for each table can be done in
this table.
The pAutoAuditRebuild stored procedure is dependant on the data in
this table to work.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.AuditAllExclusions Table (new for 3.00)
This table contains a user-defined list or base tables to exclude
when the pAutoAuditAll stored procedure is executed.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.vAudit view (new for 3.00)
This view retrieves data from AuditHeader and AuditDetail to produce
a recordset with the same structure as the Audit table had in
Version 2.00h.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.vAuditArchive view (new for 3.00)
This view retrieves data from AuditHeaderArchive and
AuditDetailArchive to produce a recordset with the same structure
as the Audit table had in Version 2.00h.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.vAuditAll view (new for 3.00)
This view does a Union All of the data from the vAudit and
vAuditArchive tables. In essence this is a view to all the data
contained in the AutoAudit (Active and Archive) tables.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.vAuditHeaderAll view (new for 3.00)
This view does a Union All of the data from the AuditHeader and
AuditHeaderArchive tables. In essence this is a view to all the
header data contained in the AutoAudit (Active and Archive)
tables.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.vAuditDetailAll view (new for 3.00)
This view does a Union All of the data from the AuditdDetail and
AuditDetailArchive tables. In essence this is a view to all the
detail data contained in the AutoAudit (Active and Archive)
tables.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAudit Procedure
applies AutoAudit to a single table
parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
(sysname is NVARCHAR(128))
@StrictUserContext BIT (default = 1)
@LogSQL BIT (Default = 0)
@BaseTableDDL BIT (Default = 0)
@LogInsert TINYINT (Default = 2)
@LogUpdate TINYINT (Default = 2)
@LogDelete TINYINT (Default = 2)
---
pAutoAudit will make the following changes:
add columns: Created, CreatedBy, Modified, ModifiedBy,
and RowVersion if @BaseTableDDL = 1
add triggers: tablename_Audit_Insert, tablename_Audit_Update,
tablename_Audit_Delete
add views: <AuditViewSchema>.vtablename_Deleted,
<AuditViewSchema>.vtablename_RowHistory
add function: <AuditViewSchema>.tablename_RowHistory,
<AuditViewSchema>.tablename_TableRecovery
---
Options:
@StrictUserContext determines how user context columns are set
(user - CreatedBy and ModifiedBy, audit time - Created
and Modified)
1 = (default) user context set by server login - suser_sname()
and server time (GetDate())
0 = user context default to server values, but can be determined
by DML and are nullable.
When using @StrictUserContext = 0:
Insert: an insert DML statement can insert into the Created
and CreatedBy columns.
Update: an update DML statement can freely update the Created,
CreatedBy, Modified, and ModifiedBy columns.
Delete: delete DML statements do not include dml columns, so when
the @StrictUserContext is set to 0, the previous modified
and modified values are captured into the audit trail table.
To record the correct delete user and datetime, first touch
(update) the row's Modified and/or ModifiedBy columns.
For most applications leaving @StrictUserContext on is approriate.
Turning @StrictUserContext off is useful for two use cases:
1 - applications that manage their own user security and log into
SQL Server using a common security context. These applications
can pass the user name to AutoAudit by inserting into the base
table's CreatedBy column or updating the base table's Modified
column.
2 - when importing data from a previous database that already has
legacy audit data.
The StrictUserContext = 0 requires the BaseTableDDL option enabled,
since the CreatedBy and ModifiedBy columns are used to pass in
the user context.
---
@LogSQL determines if the SQL batch that fired the event is logged
1 = the SQL Batch is logged in the SQLStatement column
0 = (default) the SQL Batch is not logged
SQL logging is useful for debugging, however, it can **severely** BlOaT the
audit log, so it should be normally set off (or the storage team will laugh
at you when your 6 Gb database grows to 115Gb in a week ;-)
---
@BaseTableDDL determines if the Created, CreatedBy, Modified, ModifiedBy
and RowVersion columns are added to the base tables
0 = make no changes to the base tables
1 = (default) add the Created, CreatedBy, Modified, ModifiedBy, and
RowVersion columns to the base tables
Adding the Created, Modified, and RowVersion columns is appropriate for
most tables. However, some third party databases do not allow modifying
the base table.
---
@LogInsert determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. This is useful for
importing data and avoiding a false insert event in the Audit table.
When not loggin the insert, you can still get the inserted datetime
from the Created column and the update event will have the old value.
1 - The insert event is written to the AuditHeader table
2 - (default) The AuditHeader is written and all columns are written
to the AuditDetail table.
@LogUpdate determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. *** Use this option with
caution! You will not be able to re-create your data at a point in
time with this setting.
1 - The update event is written to the AuditHeader table. *** Use this option
with caution! You will not be able to re-create your data at a point in
time with this setting.
2 - (default) The AuditHeader is written and all updated columns are
written to the AuditDetail table.
@LogDelete determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. *** Use this option with
caution! You will not be able to re-create your data at a point in
time with this setting.
1 - The insert event is written to the AuditHeader table. *** Use this option
with caution! You will not be able to re-create your data at a point in
time with this setting.
2 - (default) The AuditHeader is written and all columns are written
to the AuditDetail table.
Regardless of the @LogInsert and @LogUpdate setting, the Created, CreatedBy,
Modified, ModifiedBy, and RowVersion columns on the base table are always
set if AutoAudit was added to the table with option @BaseTableDDL = 1.
---
To change the options for a table, simply re-exec the pAutoAudit proc
with the required options to re-generate the triggers for the table.
Alternatively, you can update the AutoAudit settings changes on a
table by table basis in the <AuditSchema>.AuditBaseTables table
and then execute <AuditSchema>.pAutoAuditRebuild or
<AuditSchema>.pAutoAuditRebuildAll.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditAll Procedure
Executes pAutoAudit for every basetable except for the tables listed
in the <AuditSchema>.AuditAllExclusions table.
parameters:
@StrictUserContext BIT (default = 1)
@LogSQL BIT (Default = 0)
@BaseTableDDL BIT (Default = 0)
@LogInsert TINYINT (Default = 2)
@LogUpdate TINYINT (Default = 2)
@LogDelete TINYINT (Default = 2)
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditDrop Procedure
Removes columns, triggers, views, and function
created by pAutoAudit for a single table.
parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
(sysname is NVARCHAR(128))
@DropBaseTableDDLColumns BIT (Default = 1)
0 = keeps the base table DDL columns
1 = (default) drops the base table DDL columns
@DropBaseTableTriggers BIT (Default = 1)
0 = keeps the base table AutoAudit Triggers
1 = (default) drops the base table AutoAudit Triggers
@DropBaseTableViews BIT (Default = 1)
0 = keeps the base table AutoAudit views and function
1 = (default) drops the base table AutoAudit views and function
It does not remove the audit tables or SchemaAudit
trigger or table created when this script is executed
in a database.
If your intention is to keep the AutoAudit triggers but drop the
DDL columns, you will also have to use <AuditSchema>.pAutoAuditRebuild
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditDropAll Procedure
Drops selected components of Auto
Optionally executes pAutoAuditDrop for every basetable using the
default options.
parameters:
@DropAuditTables BIT (Default = 0)
0 = (default) keeps the AutoAudit tables
1 = drops all the AutoAudit tables
@DropAuditViews BIT (Default = 0)
0 = (default) keeps the AutoAudit views
1 = drops all the AutoAudit views
@DropAuditSPs BIT (Default = 0)
0 = (default) keeps the AutoAudit SP's
1 = drops all the AutoAudit SP's
@DropAuditDDLTriggers BIT (Default = 0)
0 = (default) keeps the AutoAudit DDL Trigger
1 = drops the AutoAudit DDL Trigger
@DropBaseTableDDLColumns BIT (Default = 0)
0 = (default) keeps the base table DDL columns
1 = drops the base table DDL columns
from each base table
@DropBaseTableTriggers BIT (Default = 0)
0 = (default) keeps the base table AutoAudit Triggers
1 = drops the base table AutoAudit Triggers
from each base table
@DropBaseTableViews BIT (Default = 0)
0 = (default) keeps the base table AutoAudit views and function
1 = drops the base table AutoAudit views and function
@ConfirmAllDrop varchar(10) (Default = 'no')
'no' = (default) Does not proceed with the AllDrop SP
'yes' = proceeds with the AllDrop SP
Important:
If @DropAuditTables=1 then @DropAuditViews, @DropBaseTableTriggers,
@DropAuditSPs, @DropBaseTableViews and @DropAuditDDLTriggers
are forced to 1
When @DropAuditTables, @DropAuditViews or @DropAuditSPs
are flaged as 1, pAutoAuditDropAll removes AutoAudit components
from the database. Depending on the options the schema audit
DDL trigger and table, and the Audit tables will be removed.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditRebuild Procedure (new for 3.00)
Drops and re-creates the Audit base table triggers, views and
function. The re-created components use the settings from the
<AuditSchema>.AuditBaseTables table for the specified based table.
This AuditBaseTables table and pAutoAuditRebuild sp can be very
useful if columns have been added/modified in a base table. If
you need to make AutoAudit settings changes to one or more
tables all you need to do is change entries in the AuditBaseTables
table. If a column is added to a base table and needs to be included
in the AuditDetail list of columns, add the column name to the
ColumnNames value for that base table before rebuilding the AutoAudit
objects. For example if you want to remove SQL statement loging on
all base tables, you can update that flag for all records and simply
execute the pAutoAuditRebuild or pAutoAuditRebuildAll procedure.
parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
Important:
If "Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag"
is set to 1 in the <AuditSchema>.AuditSettings table, when the
SchemaAuditDDLTrigger database DDL trigger fires due to a base
table schema change, SchemaAuditDDLTrigger makes an automagic
call to pAutoAuditRebuild after a ALTER_TABLE event.
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditRebuildAll Procedure (new for 3.00)
Executes pAutoAuditRebuild for every basetable that has an entry
in the <AuditSchema>.AuditBaseTables table.
parameters:
<none>
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditArchive Procedure
Moves a portion of the data from the AuditHeader and AuditDetail
tables to the AuditHeaderArchive and AuditDetailArchive tables
and/or deletes Audit data permanently.
This stored procedure should be executed on a regular basis
(with SSA) to keep the live Audit tables to a reasonable size and
performance level.
parameters:
@ArchiveAfterNumberOfDays int = -1
The number of days after which the audit data will be moved
to the Archive table. If -1 is entered, then the setting from
the <AuditSchema>.AuditSettings table will be used.
@DeleteAfterNumberOfDays int = -1
The number of days after which the audit data will be permanently
deleted from the archive (or active) Audit tables. If -1 is
entered, then the setting from the <AuditSchema>.AuditSettings
table will be used.
@KeepLastEntry bit = 1
0 = keeps the base table AutoAudit views and function
1 = (default) The last Audit entry for each primary key is not
archived (even if it should based on dates) to ensure a
sequential RowVersion is produced when logging future changes.
Important:
It is recommended to leave @KeepLastEntry bit = 1 when you did not
add the DDL columns to the base table otherwise you may
end-up with multiple Audit entries with the same RowVersion.
@DeleteAfterNumberOfDays must be >= @ArchiveAfterNumberOfDays
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditSetTriggerState Procedure (new for 3.02)
Enables or disables AutoAudit triggers at the SQL Server level.
This is different from the EnabledFlag entry in the AuditBaseTables
tables. The pAutoAuditSetTriggerState SP enables or disables the
triggers at the SQL Server level where the EnabledFlag entry in the
AuditBaseTables table keeps the triggers enabled but makes the
AutoAudit exit before logging the Audit event.
parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
@InsertEnabledFlag BIT = 1 (Default = 1)
1 = the insert trigger is enabled
0 = the insert trigger is disabled
@UpdateEnabledFlag BIT = 1 (Default = 1)
1 = the update trigger is enabled
0 = the update trigger is disabled
@DeleteEnabledFlag BIT = 1 (Default = 1)
1 = the delete trigger is enabled
0 = the delete trigger is disabled
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAuditSetTriggerStateAll Procedure (new for 3.02)
Executes pAutoAuditSetTriggerState for every basetable that has an entry
in the <AuditSchema>.AuditBaseTables table.
parameters:
@InsertEnabledFlag BIT = 1 (Default = 1)
1 = the insert trigger is enabled
0 = the insert trigger is disabled
@UpdateEnabledFlag BIT = 1 (Default = 1)
1 = the update trigger is enabled
0 = the update trigger is disabled
@DeleteEnabledFlag BIT = 1 (Default = 1)
1 = the delete trigger is enabled
0 = the delete trigger is disabled
-----------------------------------------------------------------
-----------------------------------------------------------------
Development Change History
-----------------------------------
version 1.01 - Jan 15, 2007
added RowVersion column, incremented by the modified trigger
cleaned up how the tablename is written to the tablename column
added delete trigger, which just writes the table, pk, and operation ('d') to the audit table
changed [Column] to ColumnName
-----------------------------------
version 1.02 - Jan 16, 2007
fixed bug: Duplicate Columns. databases with user-defined types was causing the user-defined types to show up as system types.
added code gen to create [table]_Deleted view that returns all deleted rows for the table
-----------------------------------
version 1.03 - Jan 16, 2007
converted from cursor to Multiple Assignment Variable for building of for-each-column code
added Created, Modified, and deleted columns to _Deleted view
-----------------------------------
version 1.04 - Jan 18, 2007
minor clean-up on _Deleted view. Removed extra Primary Key Column.
-----------------------------------
version 1.05 - Jan 18, 2007
changed from writing just the delete bit to writing the whole row.
modified _Deleted view to return RowVersion
-----------------------------------
version 1.06 - Jan 30, 2007
added host_name to audit trail
improved modified trigger run-away recursive trigger detection
added basic error-trapping
-----------------------------------
version 1.07 - Feb 6, 2007
idea from Gary Lail - don't log inserts, only updates
added pRollbackAudit procedure
changed all stored procedure names to pName
CREATE PROC usp AS SELECT OBJECT_NAME( @@PROCID )
-----------------------------------
version 1.08 - June 25, 2008
case sensitive cleanup
defaults named properly
defaults and columns dropped in AutoAuditDrop proc
-----------------------------------
version 1.09 - Oct 15, 2008
fixed @tablename bug in AutoAuditDrop
changed audit time from GetDate() to inserted.Created and inserted.Modified to keep these times in synch
changed from 'data type in()' to 'data type not in (xml, varbinary, image, text)'
added support for hierarchyID tracking (from Cast to Convert)
added check: Table must have PK
added check: PK must not be HierarchyID
added RowVersion to dbo.Audit, and insert/update/delete procs
added RowHistory Table Valued Function
added SchemaAudit table and database trigger
SchemaAuditDDLTrigger also fires pAutoAudit for Alter_Table events for tables with AutoAudit
-----------------------------------
version 1.09a - Oct 18, 2008
fixed hard-coded path in _RowHistory dynamic SQL builder code
changed _RowHistory values not updated from 0 to null
-----------------------------------
version 1.09b - Oct 23, 2008
changed SchemaSchema and .Object to allow nulls for events that do not have schema.object
-----------------------------------
version 1.10 - Jan 24, 2010
issue: NULL Updates that don't actually update anything
were still updating the Modified column
and incrementing the RowVersion
fix:
eliminated the Modified trigger
moved updating the Modified Column and incrementing the version number to the Update Trigger
moved update of Created col to insert trigger
added Modified and RowVersion col to Updated
improved error reporting slightly
added capture of user's SQL Statement/Batch
added SET ARITHABORT ON : bug and fix reported by pjl on CodePlex on Jun 15 2009 at 9:35 AM
added CreatedBy and ModifiedBy columns. If names passed to tables, then this value captured for Audit trail.
-----------------------------------
version 1.10e - Mar 20, 2010
cleaned up documentation
cleaned up sysname data type for parameters
added .dbo as default to schema parameter
added drop of audit tables and ddl trigger to pAutoAuditDropAll
-----------------------------------
version 2.00 - April 5, 2010
Added StrictUserContext Option
@StrictUserContext = 1
if 1 then blocks DML inserting or updating Created, CreatedBy, Modified, ModifiedBy
if 0 then permits DML setting of Created, CreatedBy, Modified, ModifiedBy
-----------------------------------
version 2.00c - April 26, 2010
increased Application column to 128 to allow for SSIS package names
-----------------------------------
version 2.00d - May, 2010
bug fixes for StrictSUer Context
-----------------------------------
version 2.00e July, August 2010
more bug fixes for StrictSUer Context
-- Get Modified working tweak CreatedBy no updated logic
added @LogSQL option
added @BaseTableDDL option
-----------------------------------
version 2.00f July, August 2010
added @LogInsert option
-----------------------------------
version 2.00g August, 2010
removed CreatedBy, ModifiedBy from RowHistory function
added sql_variant to the list of not audited data types
it was giving the RowHistory function a conumption
Added brackets around primary key column name in RowHistory function (reported by Anthony - SQLDownUnder)
-----------------------------------
version 2.00g August, 2010
fixed drop of SchemaTable in pAutoAuditDropAll (reported by Calvin Jones)
changed StrictUserContext ModifiedBy column constraint to NOT NULL (reported by Calvin Jones)
removed variable initialization for SQL Server 2005 compatability (reported by Calvin Jones)
removed SchemaAudit from pAutoAuditAll and pAutoAuditDropAll (Reproted by jeffcj)
-----------------------------------
version 2.00i Sept, 2010
changed SYSNAME to sysname for case sensitive collation
added code to block recursive runs of the trigger
-----------------------------------
version 2.00j Oct 7, 2010
RowVersion function incorrectlty reports initial null values and the first non-null value for initial row