January 4, 2011 at 11:41 pm
Hello folks,
I would like to set up an audit for my sql server environment. I would like to audit changes that would have been done straight on database tables; that is changes that would have not been done via applications. I am using SQL Server 2008 Standard and all my applications use different logins. Any idea as to how I can achieve this?
January 4, 2011 at 11:49 pm
rmazambara (1/4/2011)
Hello folks,I would like to set up an audit for my sql server environment. I would like to audit changes that would have been done straight on database tables; that is changes that would have not been done via applications. I am using SQL Server 2008 Standard and all my applications use different logins. Any idea as to how I can achieve this?
Logins can be retrieved from a simple query from the SQL Log files.
Active Auditing in SQL2008 can be enabled under the Security tab.
You can choose from File, Security and Application levels of auditing.
Another option is to run Profiler and select the applicable settings aimed at your goal.
Remember, auditing adds extra overhead to your server.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2011 at 4:13 am
hi i write a trigger for auditing changes on my tables
take a look at it
CREATE TABLE [TBAudit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](150) NULL,
[PK] [nvarchar](500) NULL,
[Kind] [tinyint] NULL,
[Usr] [nvarchar](100) NULL,
[Application] [nvarchar](100) NULL,
[Date] [datetime] NULL,
[Note] [nvarchar](4000) NULL,
[Seen] [bit] NULL,
[HostIP] [nvarchar](50) NULL,
CONSTRAINT [PK_TBAudit] PRIMARY KEY CLUSTERED
(
[ID] 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
CREATE TRIGGER [TrgProvinceAudit]
ON [TBProvince]
AFTER INSERT,DELETE,UPDATE
As
Begin
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@PersianFieldName nvarchar(100),
@UpdatedFields varchar(max) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UserName varchar(128),
@oldValue nvarchar(max),
@newvalue nvarchar(max),
@Type tinyint,
@Schema varchar(20)
select @TableName = 'TBProvince'
select @Schema = 'dbo'
select @UserName = system_user
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName and
CONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 3
else
select @Type = 1
else
select @Type = 2
Create Table #t(val nvarchar(max))
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 = 2)
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
delete #t
insert into #t
Exec(N'Select Cast('+@fieldname+' As Varchar(500)) From #del')
select top 1 @oldvalue = val from #t
delete #t
insert into #t
Exec(N'Select Cast('+@fieldname+' As Varchar(500)) From #ins')
select top 1 @Newvalue = val from #t
Select @PersianFieldName = Cast(value As Nvarchar(max))
from sys.extended_properties
where major_id = OBJECT_ID(@Schema+'.'+@TableName, 'U') And COL_NAME(major_id, minor_id) = @FieldName
if @Type = 1
Set @UpdatedFields = coalesce(@UpdatedFields+CHAr(13)+CHAR(10), '') +ISnull(@PersianFieldName, @FieldName)+ ' = "'+isnull(@newValue, '')+'"'
else if @Type = 2
Set @UpdatedFields = coalesce(@UpdatedFields+CHAr(13)+CHAR(10), '') +ISnull(@PersianFieldName, @FieldName)+ ' = "'+Isnull(@oldvalue, '') +'"'
else if @Type = 3
Set @UpdatedFields = coalesce(@UpdatedFields+CHAr(13)+CHAR(10), '') +ISnull(@PersianFieldName, @FieldName)+ ' = "'+Isnull(@oldvalue, '') +'" == "'+isnull(@newValue, '')+'"'
end
end
Declare @App nvarchar(300), @hostName nvarchar(100)
Select Top 1 @Hostname = hostname, @App = program_name
From sys.sysprocesses
Where spid = @@SPID
Declare @pk NVarchar(500)
Declare Crs Cursor
For
Select c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName and
CONSTRAINT_TYPE = 'PRIMARY KEY'
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
Open Crs
Fetch Next From Crs
Into @fieldname
While @@FETCH_STATUS = 0
begin
delete #t
if @type = 1 or @type = 3
insert into #t
Exec(N'Select Cast('+@fieldname+' As Varchar(500)) From #ins')
else
insert into #t
Exec(N'Select Cast('+@fieldname+' As Varchar(500)) From #del')
select top 1 @pk = coalesce(@PK+',', '')+val from #t
Fetch Next From Crs
Into @fieldname
end
Close Crs
Deallocate Crs
Drop Table #t
insert into fnc.tbaudit
values('dbo.'+@tableName, @pk, @Type, @Username, @App, GetDate(), @UpdatedFields, 0, @HostName)
End
GO
January 7, 2011 at 1:07 am
You can also consider Change Tracking or Change Data Capture. Both functionalities come with SQL 2008.
January 7, 2011 at 7:03 am
sag999 (1/7/2011)
You can also consider Change Tracking or Change Data Capture. Both functionalities come with SQL 2008.
Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply