Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Track select statements.

Author  Topic 

alexkreyn
Starting Member

9 Posts

Posted - 2007-08-29 : 16:42:11
different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists)
I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements...
1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?)
2.If somebody know where we can take good parser which will help with this job?
Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-29 : 16:56:21
Just out of curiosity, why do you need to track every select or every statement run by developers?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

alexkreyn
Starting Member

9 Posts

Posted - 2007-08-29 : 17:09:18
I want users to create hook-up reports theirself...They can use any data from database they need...But they should not open some private data without special need... The same concept people use in internet...You can open any site while you are in the office, but your boss want to grab all pornolinks you opened within working day in order to...watch them himself:))
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-29 : 19:19:08
quote:
Originally posted by alexkreyn

I want users to create hook-up reports theirself...They can use any data from database they need...But they should not open some private data without special need... The same concept people use in internet...You can open any site while you are in the office, but your boss want to grab all pornolinks you opened within working day in order to...watch them himself:))



Well we dont get to browse any porn at work, though we do have access to internet. We cant view any online streaming.

You can have trace procedures running every 30 secs or so and dumping the results into a table that you can query later.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-08-30 : 07:29:16
simple....remove direct read access to the database....

when the user come looking for info/report....formalise them by writing them as SP's...with params if necessary and then have the SP's log their exeuction...mgmt can review the logs.
Go to Top of Page

alexkreyn
Starting Member

9 Posts

Posted - 2007-08-30 : 09:29:09
The mgmt can review the trace too...It is not an issue...The problem is how to automate this process? The mgmt do not want every day to look trace. They want notification email what tables were touched, what columns were displayed and who run that query? To do this I need to go through trace every night and look select statements...Long and not reliable process. Even more. Suppose I have view from five- six tables linked together...When I say select * from view the data came from phisical table, so I want to know what table was touched and which column was displayed without been involved in sql statements overview...Like trigger for select statement(doesn't exist)...As soon as SQL server has a request to display data from table the trigger on this table should say: User A try to see my data from column a...Other words how to create event handler on display table data?
Thanks for any help
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 09:48:50
"Other words how to create event handler on display table data?"

I don't think you can (unless there is some 3rd party monitoring software).

SQL Profiler is the only way I can think of ... and then lots of work to Parse and Collate - e.g. VIEWs but also if they call an Sproc that in turn uses some Tables / Views.

Kristen
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-30 : 21:26:07
Or use third party sql auditing tools.
Go to Top of Page

alexkreyn
Starting Member

9 Posts

Posted - 2007-08-31 : 10:23:23
quote:
Originally posted by rmiao

Or use third party sql auditing tools.


Ok do you know any tool like that?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-31 : 15:08:45
Lumigent's Audit DB is one.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-03 : 16:33:01
you can run a job that retrieves information from master..sysprocesses and schedule every n-minutes...

you just need to do a lot of filtering, probably use the datestamp to check for occurrences

--------------------
keeping it simple...
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-03 : 21:44:45
May unable to capture all activities that way.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-04 : 03:56:24
you can enable C2 auditing that comes with sql server....

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 15:05:56
MAY being the keyword.. if there's a will and you don't have the money to spend, there's a way

quote:
Originally posted by rmiao

May unable to capture all activities that way.



--------------------
keeping it simple...
Go to Top of Page

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2007-09-11 : 01:55:35
The best way out is to gather the data of profiler into a table, and write dml statements to massage the data and create a report of it. But that would be lot of work.

The last resort if you can afford for third party tools is but an auditing tool.


RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.
Go to Top of Page
   

- Advertisement -