Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

auditing Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 1:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 272, Visits: 334
Hi,

How can i set column level auditing to mointor select insert update events in a table through profiler.


Regards
Prasanna
Post #621850
Posted Thursday, December 18, 2008 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 11,157, Visits: 12,899
What do you mean by column level auditing? Do you want to filter events by the columns available in Profiler? Or, do you want to be able to see when only a specific column is changed in a table?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #622185
Posted Friday, December 19, 2008 2:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 272, Visits: 334
Hi Jack,

We have applied table level trace using profile for event like select ,ineset ,update,alter on all the tables in the database into a table
we got a msg in the trace table ie.Some trace events have not been reported to SQL Server Profiler because the server has reached its maximum available memory for the process.Apart from using ddl trigger
is there any way we can use Profiler for particular column in tables for a
database which contains sensitive information.

Prasanna.
Post #622704
Posted Friday, December 19, 2008 6:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 11,157, Visits: 12,899
Prasanna,

SQL Server does not produce any trace events based on table columns. If you need to make sure you do not miss any data with a trace you need to get away from using the Profiler GUI and use a server-side trace that writes to a file. You can then use fn_trace_gettable to read/query the trace file. You can use Profiler to setup the trace definition and export it to a sql file.

There are some videos on Profiler at www.jumpstarttv.com that can help with this and you should read up on SQL Trace in BOL.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #622849
Posted Tuesday, August 17, 2010 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:48 AM
Points: 5, Visits: 96
Prasanna,

I used this script to get information about Functions and Updated/Selected columns. I hope this willl atleast give you an idea about it.

You can also set an auditing trigger for DDL statements that will provide you in depth on what DDL ran on which column or table in database.

Please read the below blog:
http://www.sqlservercentral.com/articles/DDL+Event/70657/

They have scripts in a .Zip file. I have not used that yet but I guess this will provide you more programming knowledge on how to set those auditing triggers.


Create table #USP_Dependents

(Usp_name varchar(300),
Dependent_name varchar(300),
type varchar(50),
updated varchar(50),
selected varchar(50),
[column]varchar(50)
)
Create table #USP_Dep
(Dependent_name varchar(300),
type varchar(50),
updated varchar(50),
selected varchar(50),
[column]varchar(50)
)

declare @name varchar(500)
declare cur_depend Cursor for
Select distinct name from sysobjects
where xtype = 'FN'
and name like 'IS%'
open cur_depend
fetch next from cur_depend into @name

while @@FETCH_STATUS = 0
begin

INSERT INTO #USP_Dep EXEC sp_depends @name

INSERT INTO #USP_Dependents(Usp_name,Dependent_name,type,updated,selected,[column])
select @name,Dependent_name,type,updated,selected,[column]
from #USP_dep

delete from #USP_Dep
fetch next from cur_depend into @name

end

close cur_depend
deallocate cur_depend

Select * from #USP_Dependents
Post #970307
Posted Friday, July 27, 2012 10:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:55 PM
Points: 244, Visits: 435
I want to capture specific events like column level modifications on a table. Is it possible to go granular in capturing this by enabling audits at database level?

I want to do this for auditing purposes. I wan to capture this events and load the changes to another table using ssis package where this ssis package pulls data from sql audit file.


Any suggestion is greatly appreciated.



Thanks
Post #1336649
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse