| Author |
Topic |
|
dmaxj
Posting Yak Master
174 Posts |
 Posted - 2011-02-04 : 16:54:51
|
| I am interested in knowing who access a particular column in a table.I am looking at trace events here - http://msdn.microsoft.com/en-us/library/ms186265%28v=SQL.90%29.aspxBut, I do not see an event that covers a SELECT, or DELETE, etc on a table, let alone a column. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-04 : 17:01:26
|
| There aren't specific events for those statements. You'd have to trace TSQL:StmtStarting and/or TSQL:StmtCompleted events and set filters on DatabaseName on TextData (Like '%myTable%'). You'd have to leave the trace running and periodically analyze the TextData column in the results. |
 |
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-05 : 09:52:15
|
| Thanks, robvolk!Your help works like a charm in my server-side trace. This does give rise to my next question. Is it possible to call RAISERROR from my server-side trace that running? I would like to route my error to the SQL Log instead of a file.Regards |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-05 : 10:12:33
|
| You can use RAISERROR...WITH LOG to put the message in the SQL Server log, but not from the trace. If you were using SQL 2008 I'd recommend looking at extended events for something like that. You might want to look at query notifications, you might be able to get somewhere with them (caveat: I've never used either myself, so I can't say for certain) |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-06 : 01:01:57
|
| Bummer - it seems like there should be some way to pipe the contents of the trace file that is capturing events to the SQL Log. I have tried to add WITH LOG to the sp_trace_create proc with no luck.Any other ideas? Thank you....Regards |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-06 : 07:44:48
|
| I can't say without more detail on what you're trying to do. Detect unauthorized access to that column? Generate usage statistics? Something else?I can say you really don't want to hack a trace to generate log records in SQL Server, there are better ways to do that. I hope you didn't modify the sp_trace_create procedure, modifying system procedures is a great way to break SQL Server. |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-06 : 23:35:43
|
| No, I didn't modify any system procs - that would be my very last resort.My actual goal is to write an event to the SQL Log if/when a certain user accesses a certain column.I have explored the following options the server-side trace, but seem to be stuck because I can't write an event to the SQL Log via server-side trace. My next option is to look into the Service Broker, but I have nothing concrete yet.Can Service Broker help? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-07 : 04:08:20
|
| Well...this would definitely be considered as a hack, but what if you create a profiler trace that logs it's data to a table. Then you put a trigger on the table with the RAISERROR ... WITH LOG event. Just make sure that the severity and state are low enough to not terminate the statement. Would that work for you?- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-07 : 18:31:48
|
| Lumbago, this just may be my only option.It may take a while for me to put all this together without breaking too much, but I will post back with results. I was trying to shy away from introducing 'excessive' layers, so I considered looking into Service Broker. Nevertheless, I will explore both options - Thanks for the idea!Cheers |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-08 : 05:50:51
|
| I think it actually might work well...pretty easy to set up and you can start/stop the trace whenever you want. And even though it introduces a few layers it should be pretty robust since it's all running inside the SQL Server core engine.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-18 : 13:36:59
|
| I have been working on this off and on and I think that I have run into a stumbling block.I go to SQL Profiler and create a new trace. I set my events and filters. I set the table that I want to trace to and capture the events. I start my trace and then stop the trace. I, then, export the trace definition file.Next, I open my trace file in SSMS. I try to execute the file with hopes that the trace will start, but I get an error:'The trace file path is not valid or not supported'It was working fine in Profiler, but once I attempted execute in SSMS, it doesn't work... Am I missing a step???I am just trying to get my trace to run in the background.Regards |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-18 : 13:48:07
|
| It scripted out a path that doesn't exist on the SQL Server being traced. Check the script and modify it accordingly. I strongly suggest NOT setting the trace file to the C: drive, any drive that has data or transaction log files on it, or the root folder of any drive. |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-18 : 14:45:32
|
| I guess that I don't really follow. I am not using a trace file to a disk path, I am using a table instead.How can I set the trace file to point to my trace table after opening the script definition file in SSMS? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-18 : 14:56:33
|
| To my knowledge you can't run a scripted server trace to a table, only to a file. Profiler intercepts the trace output and redirects it to a table, but it has to be running to do so.You can always configure a server trace to a file, then use fn_trace_gettable() to select data from the file(s), and optionally insert into a table. You just have to keep track of which data was already handled to avoid duplicate inserts. |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-02-21 : 22:59:12
|
| Well, I guess I am back at square one... I will post new discoveries....Regards |
|
|
|
|
server-side trace when column accessed