MSQL auditing

[dimon74]

Hello.

I'm dot.net developer, and i'm working a lot with MSQL. My current task is when some user executes select query on database, i should "catch" the query data. What do i mean is if select query is "select * from UsersTbl where UserID = 5", i have to get '5'. Does somebody have any idea where to start???

Thanks a lot.

Dmitry

 

[simian336]

Just a few questions to clarify...

Is this on a single table?
Is this a temporary thing or something that you need perminantly?
Is there a reason you wouldn't want to do it thru your front end, like a log table?

Simi

 

[markros]

It's a complex and tricky problem, but take a look at Adam Haines solution from this MSDN thread

http://social.msdn.microsoft.com/Fo...c52317e/#d9f5fcb6-d744-4ea2-943a-5c9e7315bb88

PluralSight Learning Library

 

[TheBugSlayer]

Dimon74,

Since you need to save transactions at any rate, I suggest you create a SQL Server Profiler trace and capture the data to a table, preferably not on the same server to avoid taxing it further. You can start with the built in Trace Templates (Standard or TSQL), modify them to record only the minimum information necessary; save only as many columns as you need. TextData is definitely one that you need. Once you have that then you can query the trace table and look for values in the text of the TextData columns.

If this is your solution, I will be glad to give you more details should you require them.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[dimon74]

Thanks to every body.

To simian336: 1. Yes, it is on a single table. 2. I need to control it (start/stop auditing).3.Front end? What do you mean?

To markros: Thanks, I will check it later.

Now I'm testing Event Notification in SQL.....

 

[dimon74]

To TheBugSlayer,
Is it possible to control trace listening at run time? When to start/stop? What table to log?

 

[TheBugSlayer]

Dimon74

A front-end (as opposed the back-end, the DBMS in this case) is an application through which the user interacts with the database; it could be a Windows forms app, a web app, etc...In other words, you can write a little app that allows you to start, stop, pause, configure auditing, the recording of trace events, etc. Check this link on how to programmatically manage traces

http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[dimon74]

TheBugSlayer,
Probably, this is what i need. Thank you very much!!!

 

[simian336]

On the front end...Thru your application, I was think that you have a log table and at the same time you run your query you also insert into your log some identifer and the value you want to track like....

id, UserID
1000, 5

or

Datetime, Userid
1/1/2010 12:00:00, 5


Simi

 

[TheBugSlayer]

Simian336, of course you can log all SQL statements that your application executes, and the values passed to them along with the arguments to your stored procedures. But this will be limited only to statements generated by your application. It will surely make your code a little longer...But SQL Server already offer the tool by way of its Profiler utility; you should take advantage of it.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)