| Author |
Topic |
|
Lamarrk
Starting Member
3 Posts |
 Posted - 2009-10-07 : 22:11:27
|
| I need some help to figure this one out. OS: Windows with MS SQL.I need to monitor what a dba is doing in a sql database. How would you go about it?With a couple of caveats: you cannot tip him off he's being watched, and he cleans up afterwards. The most likely scenario is that he creates some tables inside the database, gives an unauthorized person access to them, then deletes the tables he created.There's a 5 hour window during the day that this may be going on.Any ideas?Lamarr |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-07 : 22:51:02
|
| I'd start with SQL Profiler. You can run it from a remote machine and it can record all SQL Server activity (although it may drop events under heavy load). Even if he spots it running, it would be more incriminating if he terminated it, which is very difficult to do. There's also a built-in black box trace for SQL Server you can activate, but it only captures a certain amount of activity before rolling over.Your network admins can also trace all network traffic to the SQL Server on port 1433, the default TCP/IP port. This may not be feasible if connecting via Named Pipes, using encrypted connections, or if the people involved are making local connections to SQL Server through an RDP/Terminal Services or other remote client like VNC. There may be other ways to monitor such connections though. The problem is that raw network traffic will need a lot of cleanup to discern SQL Server activity.My suggestion is to get the support of your CIO/CTO/CEO or whoever is the highest officer for technology in your company before you do the monitoring, and be sure you are following your company's IT policies. If they authorize you to run Profiler against him, and he terminates the trace, I would consider that evidence enough to question him about what he's doing. He'd have very few legitimate reasons to kill a "performance" trace on the system. You could set up 2 or more traces so that one of them could catch a deliberate termination. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-08 : 03:11:15
|
Wow, this sounds exiting! I'd love to do this kind of hunting...if you don't mind I'd be very keen to hear how it goes  - Lumbagohttp://xkcd.com/327/ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-10-08 : 08:56:39
|
| If he is using a later version of SQL Server you could surreptitiously put DDL triggers on the database to record the creation and deletion of tables.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Lamarrk
Starting Member
3 Posts |
Posted - 2009-10-08 : 12:07:50
|
| Thanks for the suggestions. I'll try to post the outcome here if anything comes of it.I do appreciate the replies.Lamarr |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-10-08 : 12:47:45
|
quote:
Originally posted by blindman
If he is using a later version of SQL Server you could surreptitiously put DDL triggers on the database to record the creation and deletion of tables.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
Yes but he would see thoseProfiler is the way to goBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-10-08 : 13:06:09
|
| Only if he was looking for them....________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-10-08 : 13:21:42
|
quote:
Originally posted by blindman
Only if he was looking for them....________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
And if s/he wasn't...s/he would be easy to catchBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
winterh
Posting Yak Master
127 Posts |
Posted - 2009-10-09 : 09:18:34
|
| OO, Scandalous![ /fail at query] |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-10-09 : 10:38:38
|
| I'd agree with both methods but would angle towards the profiler, if he spots the DDL triggers that have been created he may then suspect that he or she is being monitored |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-10-09 : 11:25:11
|
It this is SQL Server 2005 or 2008, there is already a default trace running that will catch the activity you are looking for.declare @path nvarchar(1000)select @path=path from sys.traces where id = 1select * into #t from fn_trace_gettable(@path,default)select * from #t where --- put your search criteria here --- CODO ERGO SUM |
|
|
|
Lamarrk
Starting Member
3 Posts |
Posted - 2009-10-14 : 12:01:41
|
| Great suggestions guys. Thanks so much for the help!We're going to try a few and see what happens. |
|
|
|
|
How can you catch a dishonest DBA?